topic Re: Year over Year (YOY) comparison based on Dimensions in QlikView

Year over Year (YOY) comparison based on Dimensions

Fri, 31 Jul 2015 15:03:37 GMT

Hey!

I'm trying to create a Pivot Table which show the actual (Real) and planned (Plano) values, as well as their comparisons - for some parameters. The parameters are calculated expressions, whereas the distinction between Real and Plano - and hence their comparisons - are the dimension of my table. The following picture shows what I WANT to create (the last two collums are the comparisons).

So far this next picture is what I HAVE, where Real 14/15, Real 13/14 and Plano 14/15 are the components of the Plano/Real dimension.

Could someone help me on how to calculate the comparison collunms, based on the table I have already. Btw, I want the comparison collumns to be on the same "level" as the other collumns.

Thanks!

Re: Year over Year (YOY) comparison based on Dimensions

Gysbert_Wassenaar — Fri, 31 Jul 2015 17:31:46 GMT

Can you post a qlikview document that demonstrates the problem?

Re: Year over Year (YOY) comparison based on Dimensions

Fri, 31 Jul 2015 19:47:14 GMT

Here you go!

I used only 3 parameters (lines), but it doesn't make any difference since what I want to compare are the columns (Real 14/15 over Real 13/14 and Real 14/15 over Plano 14/15)

Re: Year over Year (YOY) comparison based on Dimensions

Gysbert_Wassenaar — Sun, 02 Aug 2015 16:44:42 GMT

See attached example for a possible approach.

Re: Year over Year (YOY) comparison based on Dimensions

Sun, 02 Aug 2015 19:58:28 GMT

Please explain the X in the inline statement? What does the D,N,V stand for and what is the point?

I am trying to tabulate a list of KPIS, and include the mom, yoy, mtd, and a trendline.

Re: Year over Year (YOY) comparison based on Dimensions

Gysbert_Wassenaar — Mon, 03 Aug 2015 09:10:53 GMT

The X field is just to categorize the records so I know how to use the values.

V: use the value as it is

N: use the value as numerator in the expression

😧 use the value as denominator in the expression

Re: Year over Year (YOY) comparison based on Dimensions

jonathandienst — Mon, 03 Aug 2015 09:30:11 GMT

Neat!

Re: Year over Year (YOY) comparison based on Dimensions

Mon, 03 Aug 2015 13:34:36 GMT

Thank you very much for the help Gysbert! But is there anyway you could make this accessible on the Personal edition?

Re: Year over Year (YOY) comparison based on Dimensions

Mon, 03 Aug 2015 14:29:51 GMT

Btw, I managed to open the QVW on a friend's pc (I'm using a PE version and cannot open documents from other users anymore) but the table shows "Allocated Memory Exceeded".

Do you know what that might be and how I can solve it?

Thank you once again!

Re: Year over Year (YOY) comparison based on Dimensions

Gysbert_Wassenaar — Mon, 03 Aug 2015 16:33:27 GMT

Make sure the machine has enough ram available. About 25 MB should be enough for the example I posted. If that's not the problem then try downloading the document again. Perhaps it got corrupted the first time you downloaded it.

Re: Year over Year (YOY) comparison based on Dimensions

Tue, 04 Aug 2015 14:45:40 GMT

Gysbert,

I don't know why but I'm still getting the "Allocated Memory Exceeded" error. I downloaded it many different times. Tried to open it on a couple of colleagues' computers and still got the error. I'm pretty sure we have the processing capacity to run it (we are using i7 processors with 8gb RAM capacity running nothing but the QV application).

Could you please try to upload a different version of it, or maybe some printscreens of the solution?

Thank you a lot!

Re: Year over Year (YOY) comparison based on Dimensions

Gysbert_Wassenaar — Wed, 05 Aug 2015 05:04:07 GMT

Uploaded again.

Re: Year over Year (YOY) comparison based on Dimensions

Wed, 05 Aug 2015 17:44:53 GMT

Thank you VERY MUCH Gysbert! This is exactly what I needed!