Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
If you can make changes in the script, I would suggest taking a look here
Is there a way to do it in the Chart itself ?
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)))
It breaks if I select a particular week or if the week is not in the table.
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)))