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

[#1]

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.

[#2]

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

[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.

[#4]

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))

[sunny_talwar · Accepted solution]

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))

[#6]

Thanks alot Sir.