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: 
uridiumabs
Partner - Contributor II
Partner - Contributor II

Year comparison

Hi,

I've seen several threads about this subject, but I can't meake this to work in my App.

The App is showing hierarchical data by (year, month,  Area, Sub-Area, Provider, Location).

I just want to show a line-chart comparing the dimensions selected against previous period (year). And what I did is create a new measure (Previous Year Sales) as:

sum( {$<Year = {$(<Year-1)>}>} Sales )

But I get the same amount than the current year. I've tried with different syntax unsuccessfully.

Where is the problem here?

Thanks a lot.

1 Solution

Accepted Solutions
Not applicable

Hi Juan,

If you use the logic presented by other responses and then take out the Year from your pivot table you will get something that will work as expected.

I have attached a really simple model that hopefully illustrates what you want.

Hope this helps.

Kind regards,

Rod

View solution in original post

8 Replies
MK_QSL
MVP
MVP

Current Year

SUM({<Year = $(=Year(Today()))>}Sales)

Previous Year

SUM({<Year = {'$(=Year(Today())-1)'}>}Sales)

If you want Current and Previous Year based on User Selected Years... use Max(Year) instead of Today()

Current Year

SUM({<Year = $(=Year(Max(Year)))>}Sales)

Previous Year

SUM({<Year = {'$(=AddYears(Max(Year),-1))'}>}Sales)

Not applicable

Can you tell me are you creating Year field using derived field ?

uridiumabs
Partner - Contributor II
Partner - Contributor II
Author

Thanks for your quick response!.

Manish: I tried SUM({<Year = {'$(=AddYears(Max(Year),-1))'}>}Sales) and i'm getting zero as Sales.

Current Year it's not neccesary cause this information is in the table. I just want to calculate Sales based on current selection and Year -1.

Ashish: I don't understant what you mean derived field. I've a table  (year, month,  Area, Sub-Area, Provider, Location, Sales). I've defined a mesure for Previous Year Sales, and want to compare Sales with PY Sales, according to user selecction, ex: if user selects 2010, need to show 2010 Sales and 2009 Sales. I understand that 2010 is directly provided by table data and PY sales must be calculated according to user selection.

MK_QSL
MVP
MVP

For Current Year

=SUM({<Year = {'$(=Max(Year))'}>}Sales)

For Previous Year

=SUM({<Year = {'$(=Max(Year)-1)'}>}Sales)

Not applicable

Can u attach sample of your data, it will be helpful.

uridiumabs
Partner - Contributor II
Partner - Contributor II
Author

I've attached the App.

Hope this help clarify.

Many Thanks

Not applicable

Hi Juan,

If you use the logic presented by other responses and then take out the Year from your pivot table you will get something that will work as expected.

I have attached a really simple model that hopefully illustrates what you want.

Hope this helps.

Kind regards,

Rod

uridiumabs
Partner - Contributor II
Partner - Contributor II
Author

Hi Rod,

Seems to work!.

Thank you very much to everyone.