topic Pivot table variance Actuals vs Forecasted in QlikView

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 16:22:14 GMT

I am trying to create a difference between Actuals and Forcasted amounts in Qlikview. This is very easy to do in excel but I am trying to create this in a pivot table in QV. Basically each month we have actuals and forecast and we need to do a difference to see what the amount is between the two. Also ideally at the end we would like to see a total for actual and forecast and a total difference calculated. I have attached the ideal scenario. Any suggestions on how to make this work?

Pivot table variance Actuals vs Forecasted

Miguel_Angel_Baeyens — Mon, 15 Nov 2010 16:42:45 GMT

Hello,

I'd do a new expression as the following, based on the expression names above

([ A ] - ) /

Then in chart properties, Number tab, select "Fixed to" 2 decimals (say) and check "Show in Percent %", or number

[ A ] -

Then properties, Visual Cues, select this new expression, "Lower" set 0 (negative values will be shown in red).

Hope it helps

EDIT: Trim blanks in A, I left them on purpose not to get smileys code

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 16:55:21 GMT

I think I see where you are going but unfortunately the amounts in both columns are the same. I mean they are stored as the same variable. What is differentiating them is the scenario ( actual vs Forecast) does that make sense? so if I did it right now it would look something like this.

(amount - amount ) / amount would equal zero.

Pivot table variance Actuals vs Forecasted

Miguel_Angel_Baeyens — Mon, 15 Nov 2010 17:06:26 GMT

I'm afraid I'm missing something. I'm talking about QlikView expressions, which may be different since the values they have to return may be different (one value for forecast, one for actual sales). You may have some field (say, "Scenario") to differentiate between them. Is that correct? If so, a simple set analysis would help to get what you want:

Sum({< Scenario = {'Actual'} >} Amount)

Regards

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 17:28:11 GMT

I apologize I made it confusing. I tried something like this "

=Sum(If(Scenario='F',amount))-Sum(If(Scenario='A',amount))

but that isnt correct. It made it end up like this

Pivot table variance Actuals vs Forecasted

Miguel_Angel_Baeyens — Mon, 15 Nov 2010 17:45:08 GMT

Then try something like

Sum({< Scenario = {'A'} >} amount) - Sum({< Scenario = {'F'} >} amount)

to get the difference between them. If you are using this in a pivot table, I'd pivot the month to be horizontal, top, and set Line as Dimension.

Hope that helps

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 19:08:07 GMT

sorry I still have the personal edition... we are working on getting our company to buy it. This is part of the testing process we are doing. If I knew how to attach the file I would send it to you.

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 19:09:40 GMT

here is my testing file

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 19:48:43 GMT

try to do this:

Remove scenario dimension from used dimension

In expresions create that:

Expresion A: Sum({< Scenario = {'A'} >} amount)

Expresion F: Sum({< Scenario = {'F'} >} amount)

Expresion test: Sum({< Scenario = {'A'} >} amount) - Sum({< Scenario = {'F'} >} amount)

Is this what you want?

Fiber

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 19:54:38 GMT

Fiber this does work. I did this creating variables and it works ok. now I need to make two columns at the very end that total up all actuals( for all months) for respective line do the same for forecast and then do a difference between them. Is there an easy way to make that work?

Pivot table variance Actuals vs Forecasted

Mon, 15 Nov 2010 20:02:10 GMT

Í don´t know if you´ll like it, but I just a copy of that report (if you want) and change line dimension into rows and get out month dimesion (or move it). I would´t do anything else.

Fiber