Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

10 Replies
Miguel_Angel_Baeyens

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
Author

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.

Miguel_Angel_Baeyens

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
Author

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



Miguel_Angel_Baeyens

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
Author

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
Author

here is my testing file

Not applicable
Author

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
Author

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?