How to show the distinct values under cumulative sum

[Daya_Acc]

Hi All,

I have below result post cumulative result by using below expression:

sum(aggr(RangeSum(above (total(count(distinct Reach)),0,RowNo())),Week))

But i want to get the cumulative result as a distinct value of each week as explained in below screenshot and final result would be cumulative column.

let me know if you guys need more details.

Thanks,

Daya

[Kushal_Chawda]

@Daya_Acc  try below

sum(aggr(RangeSum(
           above(sum(aggr(count(distinct Reach),Reach))
            , 0, RowNo()))
,Week))

[Qrishna]

I dint really get how you are trying to calculate the cumsum in the 2nd picture? lets say 185 in the last cell of cumulative column, can you explain how you arrived at that summation?

[rubenmarin]

Hi, you can have a solution like the asof calendar but for weeks: https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

This way you can have each dimension accessing the data of his own row and the previous row in the expression and you could use a simple: Count(distinct {$<WeekDiff={"<2"}>} Reach)

[Daya_Acc]

Hi @Qrishna ,

185 is the distinct reach count of previous week.

[Daya_Acc]

@Kushal_Chawda ,

I tried the given expression and able to find the distinct cumulative value except 1st row. 1st row value should be same as it is already distinct count.

 Getting below output.

Week	Reach	Cumulative
WK of 06 May'24	119	62
WK of 13 May'24	80	150
WK of 20 May'24	51	170
WK of 27 May'24	25	185

i'm very close to it, could you please help here to get this resolved quickly.

Thank You!

[Kushal_Chawda · Accepted solution]

@Daya_Acc  may be try sorting in aggr. If you have Numeric week value it will be beneficial to sort it in aggr

sum(aggr(RangeSum(
           above(sum(aggr(count(distinct Reach),Reach))
            , 0, RowNo()))
,(WeekNumeric,(NUMERIC))))

or

sum(aggr(RangeSum(
           above(sum(aggr(count(distinct Reach),Reach))
            , 0, RowNo()))
,(Week,(TEXT))))

[Daya_Acc]

Hi,

I'm using the same expression as below but it is giving silly result.

sum(aggr(RangeSum(
           above(sum(aggr(count(distinct Reach),Reach))
            , 0, RowNo()))
,(WeekNumeric,(NUMERIC))))

Expecting below output.

Week	Reach	Output	Expected Output
WK of 06 May'24	119	62	119
WK of 13 May'24	80	150	150
WK of 20 May'24	51	170	170
WK of 27 May'24	25	185	185

[Kushal_Chawda]

@Daya_Acc  This expression should work. I am not sure why you need 62 for first week as actual expression itself giving 119. please share a sample app to look at

[Daya_Acc]

Please see the expected output column.

I don't need 62, it should be 119 as it is 1st week. The Reach column itself is a distinct weekly value, so I'm expecting 1st week to be the same number (119). But now it is giving 62 for 1st week which doesn't look right.