Help comparing months data in pivot table?

[#1]

Hi, Im new to qlikview so tought I would start my community journey with a question on how to compare months (data) in qlikview.

What I would like to do is to Compare data from two different months and write the varience (percent) and create arrows (trends), up or down, depending on the variance (positive or negative)

I would like to create this in a pivottable (or any other suggestions on how it can be done?). The percentage variance I would like to show in a seperate column, and the trend-arrow next to it. (See attached file)

Stores and months are set as dimensions and the varience and arrows as expressions.

Im not really sure how I should compare two months with eachother =if (Sum([Retail Price]) *Feb* > Sum ([Retail Price]) *Jan* ... ?

This is where I get stuck, tried to look around but thought this would be the best way to get a good answer.

Anyone that is up to help?

In the attached file is the data created in a pivot. Any suggestions?

Thanks in advance!

_____________________________________________________

My variables are (Stores) = Store Postcode

Sales = Sum ([Retail Price])

And months = months..

[Jason_Michaelides]

I'm guessing you want to select a single month and have the chart show totals for that month, the previous month and variance between them? If so, create a straight table chart with a single dimension, Stores, and 4 expressions:

1: Sum([Retail Price])

2: Sum({<Month={"=$(=Max(Month)-1)"}>} [Retail Price])

3: Expr2Name-Expr1Name

4: If(Expr3Name<0,a,b)

In expression 4, a and b are paths to the built in icons for up and down arrows. You will need to set the representation to Image in the expression tab. Look up the use of built-in icons as I can't remember the syntax right now!

How this helps,

Jason

[#3]

Hi!

Thanks for the answer!

Yep, that is what I would like to do, compare two months, this month with previous month (and if its possible to show a list of all months and the variances, but thats a later problem!!)

I did what was stated, however it didnt seem to get the work done... :S

The Expression, Sum({<Month={"=$(=Max(Month)-1)"}>} [Retail Price]), seems to return the same Sum as Sum([Retail Price]), when I select a specifik month... Why I dont know...

I will attach the file, maybe its something ive missed...

Thanks again!

[#4 · Accepted solution]

Hi,

I think if you change the "expr2" as follows, it should work:

Sum({<Month={$(=month(AddMonths( Max(Date),-1)))}>} [Retail Price])

best regards,

http://quickdevtips.blogspot.com

[#5]

Hi,

Change this expression

Sum({<Month={"=$(=Max(Month)-1)"}>} [Retail Price])

to

Sum({<Month={"$(=Max(Month)-1)"}>} [Retail Price])

then it should work.

[Jason_Michaelides]

Oops - yep looks like there was a typo in my answer!

[#7]

Hi Great!

Aydin's expression worked well!

Is there now a possibility to show all variances from Jan to Dec in a table without having to specificly select a specific month?

Thanks again for your time!

(Ashutosh, I got your expression returning 0 )

[#8]

Yeah I agree, the expression which I gave will only work if we have numeric months so rather than having jan, feb and so on , if we have 1,2 and so on then expression, I gave will work but that should not be case as most of the time we will have textual months.

I just removed the = sign from the already posted expression and did not look at the expression itself.

Anyways, as Aydin already gave the required expression.

..

Ashutosh

[#9]

Possibilities to show all months variances in one table (not having to self-select a month) ?

Any way of doing that?

[#10]

Hi,

Yes there are ways

     1st way is by using chart inter record functions such as above, before etc  (There may be cases where needs more conditions based on the requirements such as irregular intervals)

     2nd way is by using island table  (Can be very slow for really large data sets.)

     3rd way, you can precalculate it in your script itself and add one more field as variance. (most flexible and faster but adds one more field in your data model)

Please find the attached app which shows the 1st 2 ways as 3rd one , I believe you will be able to do it.

..

Ashutosh