Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Loading the Current Year and Previous Year

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

Something like this

Capture2.JPG

  • 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?

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Set analysis would be a better way to achieve this. Having said that, if you want to know what went wrong the way you tried and get it corrected, try like:

If(

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

          if(

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

            )

  )

View solution in original post

13 Replies
Kushal_Chawda

Current :

=count({<Year ={"$(=max(Year))"}>}Products Sold)


Previous:

=count({<Year ={"$(=max(Year)-1)"}>}Products Sold)

tamilarasu
Champion
Champion

Try,

Aggr(Only({<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Year),Year)

HirisH_V7
Master
Master

Hi,


You can use two expression for comparison it will be easy ,

Dimension :


Category


Expressions:


Previous Year

Count({< Year={$(=Max(Year)-1)}>}Products Sold)


Current year


Count({< Year={$(=Max(Year))}>}Products Sold)



Post your sample data if possbile.


HTH,

-Hirish




HirisH
Anonymous
Not applicable
Author

Hi Tamil,

This will work perfectly. But I need to include the max(UpdatedDate) for the Current Year since there is new data every month. Is there a way to do that?

tresesco
MVP
MVP

Set analysis would be a better way to achieve this. Having said that, if you want to know what went wrong the way you tried and get it corrected, try like:

If(

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

          if(

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

            )

  )

Anonymous
Not applicable
Author

Hi Harish and Kush141087‌,

I tried this using two expressions. I guess I cannot make use of Legend selection. Correct me if I'm wrong.

tamilarasu
Champion
Champion

Fine.

=Aggr(Only({<Year={'$(=Max(Year)-1)'}>+<Year={'$(=Max(Year))'},UpdatedDate={'$(=Date(Max(UpdatedDate),"DD/MM/YYYY"))'}>} Year),Year)

Change the date format accordingly.

HirisH_V7
Master
Master

Hi,

Check this App with sample data,

Prevcurryear.PNG

At Back end:

Data:

LOAD Category,

     [Updated Date],

     Month([Updated Date]) as Month,

     Year([Updated Date]) as Year,

     [Products Sold]

FROM

[\Dataforprecurr.xlsx]

(ooxml, embedded labels, table is Sheet1);

At Front end:


In Chart:

Dimensions:

Category and Year,

Expressions:

Previous and current year expressions

HTH ,

-Hirish.

HirisH
sasikanth
Master
Master

hi,

Try something like

Create one variable

vYear=max(Year)  then  calculated dim will be like

if(Year>=($(vYear)-1) ,Year)  and check the option Suppress Null values