Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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
Hi Gysbert
I replied to you on Qlikview Community.
Kind regards
Nayan
See attached qvw.
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
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.
Thank you Gysbert. Much appreciated. Enjoy your day further.
kind regards
Nayan