Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
mdb_blin
New Contributor

Re: Showing difference between 2 distinctive periods

Please see attached.

Ideally, I am trying to configure to look something like the following:

Jan

Feb

Item Category

2015

2016

Variance

Variance %

2015

2016

Variance

Variance %

Chair

100

120

20

20%

150

270

120

80%

harvinderjohal
Contributor

Re: Showing difference between 2 distinctive periods

[Item Category] is a dimension.  You can't perform a Sum on Dimension, you can perform a count on it but it isn't a numeric value that you can perform addition on. 

The Measure that you are using in the Table is [QTY].  This is what you want to look at.

Within your load script, the autocalendar created the year but for some reason, it's not allowing calculations to be performed.  To correct for that, create your own calendar.

For example - go into the Data Load Editor and 'Unlock' the script.  Add the following lines under [Posting Date]:

LOAD [Posting Date],

    year([Posting Date]) as [Posting Year],

    month([Posting Date]) as [Posting Month], ...

Second, create a new Master Measure and call it CurrentYear.  Use the following syntax:  sum({<[Posting Year] = {$(=max([Posting Year]))}>} QTY)

Test this in a new KPI Object and make sure it returns data.  If it does, you can create other measures to leverage.

If you want to see CurrentYear and PriorYear side by side in a KPI, create a second Master Measure called PriorYear and use this syntax:

sum({<[Posting Year] = {$(=max([Posting Year])-1)}>} QTY)

If you just want the variance, create another Master Measure subtracting one from the other:

sum({<[Posting Year] = {$(=max([Posting Year]))}>} QTY) - sum({<[Posting Year] = {$(=max([Posting Year])-1)}>} QTY)

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.