topic Re: How to show the distinct values under cumulative sum in App Development

How to show the distinct values under cumulative sum

Daya_Acc — Fri, 25 Oct 2024 12:17:57 GMT

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

Re: How to show the distinct values under cumulative sum

Kushal_Chawda — Fri, 25 Oct 2024 14:05:45 GMT

@Daya_Acc try below

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

Re: How to show the distinct values under cumulative sum

Qrishna — Fri, 25 Oct 2024 22:12:16 GMT

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?

Re: How to show the distinct values under cumulative sum

rubenmarin — Sat, 26 Oct 2024 06:44:45 GMT

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)

Re: How to show the distinct values under cumulative sum

Daya_Acc — Thu, 31 Oct 2024 09:09:01 GMT

Hi @Qrishna ,

185 is the distinct reach count of previous week.

Re: How to show the distinct values under cumulative sum

Daya_Acc — Mon, 04 Nov 2024 08:30:51 GMT

@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!

Re: How to show the distinct values under cumulative sum

Kushal_Chawda — Mon, 04 Nov 2024 10:24:05 GMT

@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))))

Re: How to show the distinct values under cumulative sum

Daya_Acc — Mon, 11 Nov 2024 08:32:32 GMT

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

Re: How to show the distinct values under cumulative sum

Kushal_Chawda — Mon, 11 Nov 2024 09:19:44 GMT

@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

Re: How to show the distinct values under cumulative sum

Daya_Acc — Mon, 11 Nov 2024 10:01:27 GMT

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.