Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

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

View solution in original post

5 Replies
Not applicable
Author

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

Sergey_Shuklin
Specialist
Specialist

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.

Not applicable
Author

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

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

View solution in original post

Not applicable
Author

Thanks alot Sir.