# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Search instead for
Did you mean: Not applicable

## Missing Partial Sum in pivot table

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 ???
1 Solution

Accepted Solutions  Employee

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.

6 Replies  Employee

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. Not applicable
Author

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?  Employee

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 Not applicable
Author

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.  Employee

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. Not applicable
Author

Very helpful! Thanks! 