Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Re: Re: Sub-Totals in Straight Table not adding up

See attached qvw.


talk is cheap, supply exceeds demand
8 Replies

Re: Sub-Totals in Straight Table not adding up

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

Re: Sub-Totals in Straight Table not adding up

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

Re: Re: Sub-Totals in Straight Table not adding up

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

Re: Sub-Totals in Straight Table not adding up

Hi Gysbert

I replied to you on Qlikview Community.

Kind regards

Nayan

Re: Re: Re: Sub-Totals in Straight Table not adding up

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Re: Sub-Totals in Straight Table not adding up

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

Re: Re: Sub-Totals in Straight Table not adding up

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

Re: Re: Sub-Totals in Straight Table not adding up

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

kind regards

Nayan

Community Browser