Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to calculate the forecast deviation, but qlikview didn't show sum correctly. Below I have the sample data file and expected pivot table in Qlikview. The function I used to calculate the difference is "above". Is there a better function which can achieve the same? Thanks!
---------------------------------------------------------------------------------------------------------------------------------------------------
Data:
Product Name Type of Data Qty
Product A Forecast 3
Product A Actual 4
Product B Forecast 5
Product B Actual 6
Qlikview pivot table table:
Product | Type of Data | Difference (sum(total(<[Product Name],[Type of Data]> Qty)-above(sum(total(<[Product Name],[Type of Data]> Qty)) |
A | Forecast | - |
Actual | 1 | |
B | Forecast | - |
Actual | 1 | |
| SUM | ??? |
Will need to setup two expressions. One for forecast and one for actuals. If you want to the use the data as described (having three columns: ProductName, Type of Data, Qty). Then you will need to use set analysis to create the expressions.
Forecast: Sum({$<[Type of Data] = {'Forecast'}>} Qty)
Actual: Sum({$<[Type of Data] = {'Actual'}>} Qty)
I would recommend you consider transforming the data on load so there is a Forecast field and and Actual field. Then you can use simpler equations:
Forecast: Sum(Forecast)
Actual: Sum(Actual)
Examples of both are attached.
Will need to setup two expressions. One for forecast and one for actuals. If you want to the use the data as described (having three columns: ProductName, Type of Data, Qty). Then you will need to use set analysis to create the expressions.
Forecast: Sum({$<[Type of Data] = {'Forecast'}>} Qty)
Actual: Sum({$<[Type of Data] = {'Actual'}>} Qty)
I would recommend you consider transforming the data on load so there is a Forecast field and and Actual field. Then you can use simpler equations:
Forecast: Sum(Forecast)
Actual: Sum(Actual)
Examples of both are attached.
Hi Josh,
Thank you! A follow up question, if in addition to the "Type of Data" field, I have another field like "Date". I am assuming I should use Sum(Total <>) function right? How should I trick it into Sum Total function?
Shuyu,
If you don't put the Date field into the table then you will not need to use the Total modifier. I don't think you'll need it in this case.
-Josh
Hi Josh,
You are right, I don't need the total modifier.
what is the $ sign doing there? It seems to be correct without it.
You are correct the $ sign is not strictly needed. In set analysis it represents the current selections. In other words
Sum(Sales) and Sum({$} Sales) mean exactly the same thing.
When I do set analysis I like to always put it in as a reminder that the base selection is being modified. You can use other set identifiers such as:
{1} - the full set of records (aka remove all selections)
{$1} - the previous selection
{[Group1]} - Use the selections in the alternate state call [Group1]
Search the help for 'Set Analysis'. It is fairly good at explaining things. Don't forget to look at the examples.
Very helpful! Thanks!