Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
koushikganapath
Contributor III

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

Re: Loading the Current Year and Previous Year

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

Re: Loading the Current Year and Previous Year

Current :

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


Previous:

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

Re: Loading the Current Year and Previous Year

Try,

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

HirisH_V7
Honored Contributor

Re: Loading the Current Year and Previous Year

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!”
koushikganapath
Contributor III

Re: Loading the Current Year and Previous Year

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?

MVP
MVP

Re: Loading the Current Year and Previous Year

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

koushikganapath
Contributor III

Re: Loading the Current Year and Previous Year

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.

Re: Loading the Current Year and Previous Year

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
Honored Contributor

Re: Loading the Current Year and Previous Year

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
Valued Contributor III

Re: Loading the Current Year and Previous Year

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