Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mdb_blin
New Contributor

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?

11 Replies

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?

mdb_blin
New Contributor

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?

mdb_blin
New Contributor

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%.

hja
Contributor

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]

mdb_blin
New Contributor

Re: Showing difference between 2 distinctive periods

Thank you. I will try this.

hja
Contributor

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.

mdb_blin
New Contributor

Re: Showing difference between 2 distinctive periods

Please advise.

Typed in the following by it shows 0.

I typed in the following per your request in the Measure

hja
Contributor

Re: Showing difference between 2 distinctive periods

Can you attach your app?