Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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.

View solution in original post

Not applicable

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

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

Very helpful! Thanks!