topic Re: Help needed on cumulative range sum in Straight table in QlikView

Help needed on cumulative range sum in Straight table

Anonymous — Mon, 08 Jun 2015 19:10:45 GMT

Hi All,

I need some advice on complex Range sum or set analysis expression which can accumulate Column 1 previous 12 records net value to be displayed in column2.

Here i am attaching example.

In the below excel is exported straight table from qlikview

Sum of 4A(Net Elgbl $) 12 months value is 643,910.565.26 but my expression returns incorrect value.

Can some one advice whats the wrong with formula or advice me if it is aggregating incorrectly.

4A(12M Elgbl $) Expression is

Sample qvw attached.

Appreciate your help

Re: Help needed on cumulative range sum in Straight table

sunny_talwar — Mon, 08 Jun 2015 19:30:03 GMT

Have you made any selections when you exported out the table? I am seeing different numbers for the column 4A(12M Elgbl $), but the same numbers for 4A(Net Elgbl $)

Finding it hard to understand what exactly needs to be checked and resolved.

Best,

Sunny

Re: Help needed on cumulative range sum in Straight table

swuehl — Mon, 08 Jun 2015 19:32:47 GMT

I believe this issue is caused by the fact that your field AsOfPeriod shows a load order that is not chronologic (create a list box with sort order Load order for field AsOfPeriod).

The method using rangesum with an advanced aggregation needs a properly sorted field as aggr() dimension,because aggr() always uses load order for its dimension values.

Looking at your script, I think the issue arise from use of distinct on your master calendar, which unfortunately reorders the master calendar values.

Try a master calendar without DISTINCT:

LOAD

// Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

Date(MakeDate(Year(Temp_Date),Month(Temp_Date)),'YYYYMM') as Period1,

AutoNumber(Year(Temp_Date) * 100 + Month(Temp_Date)) as [Period_Ref],

Year(Temp_Date) as [Year],

Month(Temp_Date) as [Month],

Date(Temp_Date, 'YYYY-MM') as [Year-Month],

Year(Temp_Date)&'Q' & Ceil(Month(Temp_Date) / 3) as [YYYY-QQ],

'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

LOAD

MonthStart($(vMinDate), IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

WHILE monthstart($(vMinDate), IterNo() - 1) <= $(vMaxDate);

Re: Help needed on cumulative range sum in Straight table

Anonymous — Mon, 08 Jun 2015 19:59:49 GMT

Thanks swuehl and Sunny,

Removed Distinct from MasterCalendar load but still it did not work;

Re: Help needed on cumulative range sum in Straight table

swuehl — Mon, 08 Jun 2015 20:16:47 GMT

The load order is still not correct, have a look at the list box ordered by load order.

Re: Help needed on cumulative range sum in Straight table

swuehl — Mon, 08 Jun 2015 20:22:38 GMT

You are using a binary load. Does this load contain already an AsOfPeriod field?

Re: Help needed on cumulative range sum in Straight table

Anonymous — Tue, 09 Jun 2015 03:29:06 GMT

Hi Swuehl,

Thanks your help, Now i am halfway through.. I am able to get rolling 12 months numbers If i don't apply filter or if i select year 2020 still i need to able to see same number 4A(12M Elgbl $) : 643,910,800.77 instead of 39,337,623.26.

Appreciate if your help if we can fix this with set analysis but i did not succeeded.

Attached updated qvw.

Re: Help needed on cumulative range sum in Straight table

swuehl — Tue, 09 Jun 2015 08:28:58 GMT

sum( aggr( rangesum( above(

Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductSales)

Round(Num( ((Sum({<PeriodType = {'Rolling12'}, AsOfPeriod= ,Year = >}[In EGBL Units]) + (Sum({<PeriodType = {'Rolling12'}, AsOfPeriod= ,Year = >}[In EGBL Units]) * only({<AsOfPeriod=, Year=>} Ratio) )) / Sum({<PeriodType = {'Rolling12'} , AsOfPeriod= ,Year = >} ProductGrams)),'#.00000')

Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductGrams)

)

(Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductSales) / Sum({<PeriodType = {'Current'} , AsOfPeriod= ,Year = >} ProductGrams))

,0,12) ),NDC11,AsOfPeriod))

Re: Help needed on cumulative range sum in Straight table

Anonymous — Tue, 09 Jun 2015 17:33:12 GMT

only({<AsOfPeriod=, Year=>} Ratio) Ratio is at NDC11 level and hence I don't need above statement.

I have my Range sum expression like this

sum( aggr( rangesum( above(

Expression to calculate current month Net sales

,0,12) ),NDC11,AsOfPeriod)) ===> Aggregating 12 months net sales.

Here problem i am facing is when i select Year 2020 then 2010 Jan Current Month Net Sales and 12 Months Net sales same.

I need set analysis to calculate 12 months aggregated net sales even though i selected sp0ecific year.

Re: Help needed on cumulative range sum in Straight table

swuehl — Tue, 09 Jun 2015 17:46:57 GMT

This is the table I get when selecting Year 2020.

NDC11	AsOfPeriod	1A Sales	1A Units	WAC	12M DS	12M Units	3C=3A+3B	3E	3G (In Elgbl $)	3G (In Elgbl Units)	4A(Net Elgbl $)	4A(12M Elgbl $)	4A(Net Elgbl Units)	4A(12M Elgbl Units)	6C=6A-6B	6E	7C=7A-7B	7E	8C=8A-8B	8E	CM Concessions	Final AMP $	Final AMP Price $	3F	12WAC	3G 12M (In Elgbl $)	Column(4) - [3G 12M (In Elgbl $)]
50242006001	202001	56852725.275615	293052	194.00	933107434.84	4863425	88895	1498310	17515102.02	90283	39337623.26	643910800.77	202769.35	3365115.26	545504.89	9637080.89	92312.16	1834685.90	38.45	764.61	700877.02	38636746.24	$187.088772	0.30808	191.86220158176	17529652.19	915577782.65582
50242006001	202002	59548347.568517	306947	194.00	918606913.34	4781031	93110	1471355	18325837.28	94462	41222510.29	634171109.48	212484.36	3309676.54	608803.35	9374968.30	103023.73	1770801.16	42.91	737.96	713588.26	40508922.04	$187.285941	0.30775	192.13571756926	35822315.08	882784598.25802
50242006001	202003	72174139.327536	372027	194.00	901031905.78	4681168	112851	1438684	22181631.31	114337	49992508.02	622361078.73	257690.60	3242484.48	562293.61	9132879.89	95153.20	1711796.91	39.64	713.35	842024.16	49150483.86	$187.466910	0.30733	192.48013298534	57955639.84	843076265.93473
50242006001	202004	60591404.135105	312323	194.00	886277391.26	4597332	94741	1411257	18599962.41	95875	41991441.73	612443600.40	216448.40	3186074.11	523514.79	8907487.21	88590.92	1656861.93	36.90	690.43	688979.60	41302462.13	$187.635866	0.30697	192.78082906179	76487507.47	809789883.7931
50242006001	202005	62521389.785773	322272	194.00	871052909.08	4510824	97758	1382956	19168194.90	98804	43353194.89	602208363.93	223467.53	3127867.95	548336.78	8671407.74	92791.38	1599322.24	38.65	666.43	691552.33	42661642.56	$187.812926	0.30659	193.10282004352	95559283.27	775493625.81144
50242006001	202006	68227293.875043	351683	194.00	854438991.45	4416422	106681	1352073	20887636.53	107667	47339657.34	591037938.85	244016.47	3064349.39	664598.38	8385273.31	112465.55	1529582.66	46.85	637.34	728977.10	46610680.24	$188.027073	0.30615	193.46859036715	116309760.23	738129231.21234
50242006001	202007	61072008.883429	310148	196.91	839567445.65	4333169	94081	1324837	18672400.90	94826	42399607.99	581039504.00	215322.61	3008332.39	544041.56	8151043.09	92064.53	1472493.69	38.35	613.53	633721.86	41765886.13	$191.025755	0.30574	193.75366773307	136571246.86	702996198.7963
50242006001	202008	68728532.076657	349031	196.91	822831472.32	4239479	105876	1294186	20980803.19	106549	47747728.89	569789373.09	242482.46	2945292.89	561370.62	7909352.06	94997.01	1413586.30	39.57	588.96	691365.04	47056363.85	$191.209699	0.30527	194.08787485569	157340566.29	665490906.03085
50242006001	202009	66809876.201728	339288	196.91	806562707.76	4148404	102920	1264391	20362892.55	103411	46446983.65	558856627.71	235876.21	2884013.43	612603.12	7645603.53	103666.74	1349302.84	43.18	562.14	648867.10	45798116.56	$191.410779	0.30479	194.42722175903	177455940.14	629106767.62242
50242006001	202010	58786557.812545	298542	196.91	792247688.45	4068267	90561	1238175	17891643.83	90861	40894913.98	549240490.37	207680.86	2830092.23	537316.69	7414268.62	90926.52	1292919.54	37.87	538.62	553029.81	40341884.17	$191.586529	0.30435	194.73838215928	195091401.13	597156287.31721
50242006001	202011	65607942.905372	333184	196.91	776271604.72	3978830	101069	1208916	19934214.58	101234	45673728.32	538514449.90	231950.10	2769914.17	604679.42	7153931.54	102325.87	1229467.55	42.62	512.16	594685.82	45079042.50	$191.784165	0.30384	195.10044866155	214696969.18	561574635.54754
50242006001	202012	60595801.555906	307730	196.91	761516019.40	3896227	93348	1181893	18381364.59	93348	42214436.96	528614335.32	214382.25	2714333.72	587798.93	6900862.13	99469.30	1167786.91	41.43	486.43	529008.26	41685428.70	$191.976806	0.30334	195.44962087179	232729208.35	528786811.05275

Re: Help needed on cumulative range sum in Straight table

Anonymous — Tue, 09 Jun 2015 17:53:03 GMT

Hi ,

This is what i need, Can you send me the updated file what you have

Re: Help needed on cumulative range sum in Straight table

Anonymous — Tue, 09 Jun 2015 17:56:37 GMT

Hi Swuehl,

This is what i need, Can you tell me what was the change in the expression?

Or send me updated file.

Thanks

Re: Help needed on cumulative range sum in Straight table

swuehl — Tue, 09 Jun 2015 18:10:29 GMT

See my previous post, applying only() with set expression around Ratio.

Re: Help needed on cumulative range sum in Straight table

Anonymous — Wed, 10 Jun 2015 05:36:54 GMT

Thanks that works.

Re: Help needed on cumulative range sum in Straight table

Anonymous — Fri, 12 Jun 2015 07:04:57 GMT

Can you see whats the wrong with my formula again when i select specific year then value not accumulated.

sum( aggr( rangesum( above(

(Sum({}ProductSales)

,0,12) ),NDC11,Period))

Re: Help needed on cumulative range sum in Straight table

swuehl — Fri, 12 Jun 2015 08:32:39 GMT

sum( aggr( rangesum( above(

(Sum({}ProductSales)

,0,12) ),NDC11,Period))

This is your formula? Why should this accumulate over Year bounderies when you are not clearing selection in your calendar fields like in the previous expression. I would expect at least a set expression in the inner aggregation like

{<Year= >}

[or add up all calendar fields the user may select in].

Re: Help needed on cumulative range sum in Straight table

Anonymous — Fri, 12 Jun 2015 13:55:22 GMT

Hi,

This is my Formula.

sum( aggr( rangesum( above(

((Sum({}ProductSales)

)

,0,12) ),NDC11,Period))