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

Sub-Totals in Straight Table not adding up

Hi

I have a straight table with sub-totals , however the sub-totals do not add up.

Attached herewith is an extract of my buying template which i exported to excel.   The only column where the totals dont add up is the stock required column. 

The formula for stock required is a follows:

if(Average Last 6 Months * No of Months - Stock on Hand < 0 ,0 , Average Last 6 Months * No of Months - Stock on Hand)

In the expression tab , I have changed the "Total Mode" to Sum, however its still recognising it as a "Expression Total".

Also the Grand Total for this expression is also incorrect.

I even changed the chart to pivot table and it still shows incorrect result.

Please can you help.  Do i need to change my formula for stock required.

kind regards

Nayan

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

The problem is probably the use of the expression labels in the stock expression instead of the actual expressions themselves. Can you post the .qvw file?


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert

The Qlikivew model I have is huge .  What I'll do is make a condensed version and send it to you.

kind regards

Nayan

Not applicable
Author

Hi Gysbert

Sorry for the delayed reply as i have been busy.

Attached herewith is the Sample Qlikview Model and an excel spreadhseet on which the Qlikview model is based on.

The Excel spreadheet has 2 sheet, one with Sales quantity and the other is Stock on Hand (SOH).

I have added a new field called "MonthIndex" .  This is use so that it will enable me to calculate sales for the last few months.

As mentioned in my post, the sub-total & total of the stock required is incorrect.

Thanking you in advance.

kind regards

Nayan

Not applicable
Author

Hi Gysbert

I replied to you on Qlikview Community.

Kind regards

Nayan

Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert

Thank you for the feedback.  Much appreciated. Can you explain the first path of formula.

sum(aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)

kind regards

Nayan

Gysbert_Wassenaar

sum(aggr( ...inner expression... ,Branch,[Item Code]))

What this does is aggregate the values of the inner expression over the Branch and Item Code dimensions and sums those values. The aggr function creates a virtual table with the values of the inner expression calculated for each Branch and Item Code combination as 'records'. The outer sum then sums these values.


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you Gysbert.  Much appreciated.  Enjoy your day further.

kind regards

Nayan