Loading the Current Year and Previous Year

[Anonymous]

I need to show Number of Products sold each year for different Categories.

Something like this

• Here Category will be the Primary Dimension and Year will be the Secondary Dimension.
• count(Products Sold) will be the expression.

However, I need to show the count only for Current and Previous Year. Current Year data will be updated monthly. For that purpose there is a field called UpdatedDate.

Assume, Current Year is 2015 and Previous Year is 2014. 2014 data will be same while 2015 data changes every month. I need to the bar chart accordingly.

I tried this as a Calculated Dimension

If(

     Year=(max(Year)-1), Year,

          if(

               Year=max(Year) and UpdatedDate=max(UpdatedDate),Year

            )

  )

This is not working. I know I'm wrong logically somewhere. Can someone help me on how to solve this?

[Anonymous]

Hi Tamil,

Unfortunately, the current Year data is not getting loaded. I'm not sure why it's happening. But the solution can be obtained using the technique tresesco‌ has given here. Even he mentioned, using set analysis is the better way.

Thanks.

[Anonymous]

Hi Hirish,

Thanks for your time. Using two expressions is good and I have tried this already. But the end users are very novice when it comes to qlikview. So the first thing they do is clicking the legend for Year selection.

Legends are not selected if expression is used even though they are displayed.

Thanks.

[Anonymous]

Hi tresesco‌,

If possible can you tell me how to include the updated date for previous year as well?

Say, 2015 is my Current Year and the max(UpdatedDate) should give me 2015's last Updated date.

Similarly 2014 is my previous year and the max(UpdateDate) should give me 2014's last updated date.

Literally a sub query kind.

I tried this in a plain text box to get the last updated date for Previous Year.

max({<Year={'$(=max(Year)-1)'}>}UpdatedDate)

Not Sure how to implement the same in Calculated Dimension.

[tamilarasu]

Hi Koushik,

Try,

=If(

    Year=$(=Max(Year)-1) and UpdatedDate = $(=Max({<Year={$(=Max(Year)-1)}>}UpdatedDate)), Year,

         If(

              Year=$(=Max(Year)) and UpdatedDate = $(=Max(UpdatedDate)),Year

           )

    )