11 Replies Latest reply: Aug 2, 2016 5:40 PM by Harvey Johal

# Showing difference between 2 distinctive periods

I am trying to create a measure to show the variance/difference between 2 periods.

For example, I like to show the difference between 2015Q1 vs. 2016Q1.  What's the right expression required in creating such measure in Qlik Sense?

• ###### Re: Showing difference between 2 distinctive periods

That depends.

Are you using the Quarter period as a chart dimension?

Do you select one quarter? Or do you select two quarters?

• ###### Re: Showing difference between 2 distinctive periods

Yes.  I am using quarter  period.    I do select both quartes as well.  However, what I like to see if something as follows in table format:

2016Q2    2015Q2    Variance    Variance %

• ###### Re: Showing difference between 2 distinctive periods

Can you post a small qlikview document that illustrates what you're trying to do?

• ###### Re: Showing difference between 2 distinctive periods

For example, I like to create a measure that’s able to show the difference between 2016 vs. 2015 in the month of January.

In this particular example,  for Item Category Adult Rockers, I would like to have 2 additional columns on the right showing the difference between 284 (2016) – 192 (2015) resulting in a positive variance of 92 and variance % of 47.92%.

• ###### Re: Showing difference between 2 distinctive periods

You'll need to use Set Analysis to get the Max Year and subtract the Max Year -1 for Previous Year.

ie.

Try this:

//Variance = Calculate the sales for Max Year and subtract Max Year-1 for previous years

sum({<[Year] = {\$(=max([Year]))}>} [Item Category]) - sum({<[Year] = {\$(=max([Year])-1)}>} [Item Category])

For your % difference, just divide by the above with the year you want for the increase.  ie.

(sum({<[Year] = {\$(=max([Year]))}>} [Item Category]) - sum({<[Year] = {\$(=max([Year])-1)}>} [Item Category])/sum({<[Year] = {\$(=max([Year])-1)}>} [Item Category]

• ###### Re: Showing difference between 2 distinctive periods

Thank you.  I will try this.

• ###### Re: Showing difference between 2 distinctive periods

If this works out, could you mark the post as 'helpful' or 'answered'?

One thing to note on Dimension names/Field names.  Year and Month are reserved function names in Qlik.  I would recommend renaming/aliasing your Fields in the Data Load Editor to something a bit more descriptive such as [Order Year], [Order Month], or something different so as to avoid any conflicts or errors in your application.

• ###### Re: Showing difference between 2 distinctive periods

Typed in the following by it shows 0.

I typed in the following per your request in the Measure

• ###### Re: Showing difference between 2 distinctive periods

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%

• ###### 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]:

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)