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

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

View solution in original post

6 Replies
Josh_Good
Employee
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?

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

Josh_Good
Employee
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!