topic Sub-Totals in Straight Table not adding up in QlikView

Sub-Totals in Straight Table not adding up

Fri, 07 Nov 2014 07:17:50 GMT

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

Re: Sub-Totals in Straight Table not adding up

Gysbert_Wassenaar — Fri, 07 Nov 2014 13:06:27 GMT

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?

Re: Sub-Totals in Straight Table not adding up

Fri, 07 Nov 2014 13:15:41 GMT

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

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

Tue, 11 Nov 2014 09:08:59 GMT

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

Re: Sub-Totals in Straight Table not adding up

Tue, 11 Nov 2014 09:12:42 GMT

Hi Gysbert

I replied to you on Qlikview Community.

Kind regards

Nayan

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

Gysbert_Wassenaar — Tue, 11 Nov 2014 09:20:04 GMT

See attached qvw.

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

Tue, 11 Nov 2014 09:28:35 GMT

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

Gysbert_Wassenaar — Tue, 11 Nov 2014 09:35:31 GMT

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.

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

Tue, 11 Nov 2014 09:37:54 GMT

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

kind regards

Nayan