5 Replies Latest reply: Jun 21, 2017 5:14 AM by doctor no RSS

    Aggr() for previous year if the aggregating field is date.

    doctor no

      Greetings everyone!

       

      I am facing a challenge for making an expression for previous year.

       

      In the expression in am using Aggr() and aggregating with DATE.

       

      The expression works great for Current Year and gives correct value.

      but when the same expression is used (modified) for previous year it does not work returns value 0.

       

      This is due to the current year selection, since there wont be any date which will be in this year as well as previous year.

       

      How do i exclude the current year selection and still manage to aggregate on the date field since its the only unique key in the data?

       

      Note: I have tried by passing the date field form the expression and aggregate it.




      Thanks in Advance.

        • Re: Aggr() for previous year if the aggregating field is date.
          Rima Hajou

          can you please post the expression you used for current year?

            • Re: Aggr() for previous year if the aggregating field is date.
              doctor no

              I have used the following expression

               

              sum(aggr(

              if(

              (

                          Sum({<YEAR={'$(vMaxYear)'}>}Sales)<>0         

                          AND

                          (

                          sum({<YEAR={'$(vMaxYear)'}>}A)=0

                                  OR

                          sum({<YEAR={'$(vMaxYear)'}>}A)=null()

                          )

                          AND

                          (

                          Sum({<YEAR={'$(vMaxYear)'}>}B)=0

                                  OR

                          Sum({<YEAR={'$(vMaxYear)'}>}B)=null()

                          )

              ,

                        Sum({<YEAR={'$(vMaxYear)'}>}Sales)

              ,

               

              (

                 (

                      (

                          Sum({<YEAR={'$(vMaxYear)'}>}Sales)

                           *sum({<YEAR={'$(vMaxYear)'}>}A)

                      )

                   /

                   Sum({<YEAR={'$(vMaxYear)'}>}B)

                  )

              )

              ,KEY,Date))

                • Re: Aggr() for previous year if the aggregating field is date.
                  Sunny Talwar

                  This is the expression for current year, right? I am assuming you have something like this for previous year

                   

                  Sum(Aggr(

                  if(

                  (

                              Sum({<YEAR={'$(vPrevYear)'}>}Sales)<>0     

                              AND

                              (

                              sum({<YEAR={'$(vPrevYear)'}>}A)=0

                                      OR

                              sum({<YEAR={'$(vPrevYear)'}>}A)=null()

                              )

                              AND

                              (

                              Sum({<YEAR={'$(vPrevYear)'}>}B)=0

                                      OR

                              Sum({<YEAR={'$(vPrevYear)'}>}B)=null()

                              )

                  ,

                            Sum({<YEAR={'$(vPrevYear)'}>}Sales)

                  ,

                   

                  (

                    (

                          (

                              Sum({<YEAR={'$(vPrevYear)'}>}Sales)

                              *sum({<YEAR={'$(vPrevYear)'}>}A)

                          )

                      /

                      Sum({<YEAR={'$(vPrevYear)'}>}B)

                      )

                  )

                  ,KEY,Date))

                   

                  What you need is to add the same set analysis to your outer Sum() function here

                   

                  Sum({<YEAR={'$(vPrevYear)'}>} Aggr(

                  if(

                  (

                              Sum({<YEAR={'$(vPrevYear)'}>}Sales)<>0    

                              AND

                              (

                              sum({<YEAR={'$(vPrevYear)'}>}A)=0

                                      OR

                              sum({<YEAR={'$(vPrevYear)'}>}A)=null()

                              )

                              AND

                              (

                              Sum({<YEAR={'$(vPrevYear)'}>}B)=0

                                      OR

                              Sum({<YEAR={'$(vPrevYear)'}>}B)=null()

                              )

                  ,

                            Sum({<YEAR={'$(vPrevYear)'}>}Sales)

                  ,

                   

                  (

                    (

                          (

                              Sum({<YEAR={'$(vPrevYear)'}>}Sales)

                              *sum({<YEAR={'$(vPrevYear)'}>}A)

                          )

                      /

                      Sum({<YEAR={'$(vPrevYear)'}>}B)

                      )

                  )

                  ,KEY,Date))

              • Re: Aggr() for previous year if the aggregating field is date.
                Sergey Shuklin

                Hello!

                 

                You can add an IF-condition within the aggr function where you can set a period.

                And yes, with example of expression it would be more easier to answer.