4 Replies Latest reply: Sep 21, 2018 5:46 AM by Mahitha M RSS

    Set analysis

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      In the below attached app ,In Month end Trend chart need to get 75,836 value instead of Zero. I have included the formula in the application in this chart.

       

      75836.png

       

      The values are coming like In Jul Month Max Reporting date is 19/07/2018 that date value 75,836 need to show on trend same for aug.

      For aug getting correct value as max(date) value.

      Jul max date.png

      Aug Max date.png

       

       

       

       

      Please help me to show all the months Max(Date) values by default.

       

      Thanks in advance.

        • Re: Set analysis
          Andrey Krylov

          Hi.Try this expression

          =Sum(Aggr((Sum({<Type={'A'},Date={$(=Chr(39)&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), Chr(39)&Chr(44)& Chr(39))& Chr(39))}>}Amount)* Avg({<Date={$(=Chr(39)&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), Chr(39)&Chr(44)& Chr(39))& Chr(39))}>}A1%)),Date,Dept,Branch))

          or the same but a little bit shorter

          =Sum(Aggr((Sum({<Type={'A'},Date={$(='"'&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), '","')&'"')}>}Amount)* Avg({<Date={$(='"'&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), '","')&'"')}>}A1%)),Date,Dept,Branch))

          • Re: Set analysis
            Gabor Tarnoczai

            Hi Mahita,

             

            I can suggest you to create a flag in the load script, that indicate the last day of month, the max([Report Date]) returns the max date of the whole application, not by dimension.

             

            G.

            • Re: Set analysis
              Mahitha M

              Hi,

               

              I have found the solution.

              Below Expression is working fine.

               

              =Sum(Aggr((Sum({<Type={'A'},Date=p(New_Date)>}Amount)*

              Avg({<Date=p(New_Date)>}A1%)),Date,Dept,Branch))