Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
“Aspire to Inspire before we Expire!”
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
“Aspire to Inspire before we Expire!”
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