Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
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
MVP & Luminary
MVP & Luminary

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?


talk is cheap, supply exceeds demand
Highlighted
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 %

Highlighted
MVP & Luminary
MVP & Luminary

Re: Showing difference between 2 distinctive periods

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


talk is cheap, supply exceeds demand
Highlighted
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%.

Highlighted
harvinderjohal
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]

Highlighted
mdb_blin
New Contributor

Re: Showing difference between 2 distinctive periods

Thank you. I will try this.

Highlighted
harvinderjohal
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.

Highlighted
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

Highlighted
harvinderjohal
Contributor

Re: Showing difference between 2 distinctive periods

Can you attach your app?