Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Pivot table variance Actuals vs Forecasted

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?

error loading image

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Pivot table variance Actuals vs Forecasted

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

10 Replies
MVP
MVP

Pivot table variance Actuals vs Forecasted

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

Not applicable

Pivot table variance Actuals vs Forecasted

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.

MVP
MVP

Pivot table variance Actuals vs Forecasted

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

Not applicable

Pivot table variance Actuals vs Forecasted

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



MVP
MVP

Pivot table variance Actuals vs Forecasted

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

Not applicable

Pivot table variance Actuals vs Forecasted

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.

Not applicable

Pivot table variance Actuals vs Forecasted

here is my testing file

Not applicable

Pivot table variance Actuals vs Forecasted

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

Not applicable

Pivot table variance Actuals vs Forecasted

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?

Community Browser