topic Re: Missing Partial Sum in pivot table in QlikView

Missing Partial Sum in pivot table

Wed, 20 Nov 2013 14:45:40 GMT

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	-
A	Actual	1
B	Forecast	-
B	Actual	1
	SUM	???

Re: Missing Partial Sum in pivot table

Josh_Good — Wed, 20 Nov 2013 16:30:57 GMT

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.

Re: Missing Partial Sum in pivot table

Wed, 20 Nov 2013 16:58:06 GMT

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?

Re: Missing Partial Sum in pivot table

Josh_Good — Wed, 20 Nov 2013 17:07:44 GMT

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

Re: Missing Partial Sum in pivot table

Wed, 20 Nov 2013 19:06:46 GMT

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.

Re: Missing Partial Sum in pivot table

Josh_Good — Wed, 20 Nov 2013 21:26:59 GMT

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.

Re: Missing Partial Sum in pivot table

Wed, 20 Nov 2013 21:31:28 GMT

Very helpful! Thanks!