topic Re: Cumulative week sum aggregated at Quarter Level? in QlikView

Cumulative week sum aggregated at Quarter Level?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can someone help getting cumulative week sum to aggregate at Quarter level. This expression will be part of table which can be converted to a straight table from a pivot table or viceversa.

Also, when in the middle of the quarter, it should accumulate until that week in the quarter.

When user selects just quarters then it should just show the cumulative sum .@

I really appreciate you taking time to help with this:)

YYYYQ	YYYYWK	Sum	Cumulative Sum(Expected Results
20171	201701	77	77
20171	201702	96	173
20171	201703	87	260
20171	201704	83	342
20171	201705	71	414
20171	201706	108	522
20171	201707	95	617
20171	201708	97	714
20171	201709	87	801
20171	201710	102	903
20171	201711	99	1002
20171	201712	116	1118
20171	201713	139	1257
20172	201714	83	83
20172	201715	92	176
20172	201716	92	268
20172	201717	98	366
20172	201718	82	449
20172	201719	102	551
20172	201720	97	648
20172	201721	106	754
20172	201722	99	853
20172	201723	104	957
20172	201724	109	1067
20172	201725	115	1181
20172	201726	130	1311
20173	201727	90	90
20173	201728	92	181
20173	201729	93	275
20173	201730	102	377
20173	201731	105	482
20173	201732	99	581
20173	201733	99	679
20173	201734	102	782
20173	201735	104	886
20173	201736	116	1001
20173	201737	107	1109
20173	201738	119	1228
20173	201739	147	1375
20174	201740	78	78
20174	201741	103	181
20174	201742	112	292
20174	201743	88	380
20174	201744	79	460
20174	201745	106	565
20174	201746	102	667
20174	201747	106	773
20174	201748	104	877
20174	201749	116	993
20174	201750	111	1105
20174	201751	117	1221
20174	201752	144	1366

Re: Cumulative week sum aggregated at Quarter Level?

sunny_talwar — Thu, 29 Mar 2018 18:09:04 GMT

If you can make changes in the script, I would suggest taking a look here

The As-Of Table

Re: Cumulative week sum aggregated at Quarter Level?

Anonymous — Thu, 29 Mar 2018 18:16:50 GMT

Is there a way to do it in the Chart itself ?

Re: Cumulative week sum aggregated at Quarter Level?

sunny_talwar — Thu, 29 Mar 2018 18:18:56 GMT

May be this

Aggr(RangeSum(Above(Sum(Sum), 0, RowNo())), YYYYQ, YYYYWK)

Or this if you are using QV12 or above or Qlik Sense

Aggr(RangeSum(Above(Sum(Sum), 0, RowNo())), YYYYQ, (YYYYWK, (NUMERIC)))

Re: Cumulative week sum aggregated at Quarter Level?

Anonymous — Thu, 29 Mar 2018 18:30:48 GMT

It breaks if I select a particular week or if the week is not in the table.

Re: Cumulative week sum aggregated at Quarter Level?

sunny_talwar — Thu, 29 Mar 2018 18:35:08 GMT

For selection of week... try this

Aggr(RangeSum(Above(Sum({<YYYYWK>}Sum), 0, RowNo())), YYYYQ, YYYYWK)

Aggr(RangeSum(Above(Sum({<YYYYWK>}Sum), 0, RowNo())), YYYYQ, (YYYYWK, (NUMERIC)))