topic Help comparing months data in pivot table? in QlikView

Help comparing months data in pivot table?

Sun, 20 May 2012 10:21:21 GMT

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

Help comparing months data in pivot table?

Jason_Michaelides — Sun, 20 May 2012 15:05:54 GMT

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

Re: Help comparing months data in pivot table?

Sun, 20 May 2012 19:39:04 GMT

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!

Re: Help comparing months data in pivot table?

Mon, 21 May 2012 03:30:26 GMT

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

Help comparing months data in pivot table?

Mon, 21 May 2012 05:54:59 GMT

Hi,

Change this expression

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

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

then it should work.

Help comparing months data in pivot table?

Jason_Michaelides — Mon, 21 May 2012 06:59:32 GMT

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

Re: Help comparing months data in pivot table?

Tue, 22 May 2012 13:32:17 GMT

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 )

Re: Help comparing months data in pivot table?

Tue, 22 May 2012 18:57:23 GMT

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

Re: Help comparing months data in pivot table?

Wed, 23 May 2012 18:53:35 GMT

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

Any way of doing that?

Re: Help comparing months data in pivot table?

Wed, 23 May 2012 19:06:04 GMT

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

Re: Help comparing months data in pivot table?

Wed, 23 May 2012 21:55:55 GMT

Do you need the something like the "Total" table (I named it as "total", it shows Jan to Apr and diference between Jan & Feb, Feb & Mar, Mar & Apr)

in the attached file?

Best regards,

Re: Help comparing months data in pivot table?

Wed, 23 May 2012 23:37:48 GMT

Hey, Nice!

Great illustrations Ashutosh! Specially the island chart!

I ended up using Ashutosh suggestion using the chart inter record functions,

My final ended up:

Pivot table:

Dimension: Stores, Months

Expressions,

1. sum([Retail Price])

2. RangeSum(sum([Retail Price]),-Before(sum([Retail Price])))/Before(sum([Retail Price]))

3. if(Variance>0,'qmem://<bundled>/BuiltIn/arrow_ne_g.png','qmem://<bundled>/BuiltIn/arrow_sw_r.png')

Might sound little strange, but seems to actually work for my purpose!

Aydin thanks! Yep thats it, sum({$ <Month={'Jan'}>} [Retail Price]), so thats how you get 'Jan'

Ashutosh, Bilge Aydin and Jason, thank you for your time!

Re: Help comparing months data in pivot table?

yakir_manor — Thu, 18 Feb 2016 14:39:12 GMT

cool, this is exactly what i wanted, i attached my Qvw with your changes (: