topic Re: Cumulative sum should not change based on selection in App Development

Cumulative sum should not change based on selection

KNRaju105630 — Sat, 25 Mar 2023 03:20:09 GMT

Hi Experts,

I'm doing cumulative sum - that is working fine, but if user select any product cumulative value should not be change.

below is my cumulative sum and I'm selecting one product as Tab, here cumulative sum has changed to 10.

Cumulative Sum: RangeSum(Above(TOTAL Sum(Sales), 0, RowNo(TOTAL)))

I did some work around when user select product, finding MIN and MAX value

like Min(Sales) and max({<Product=>}Sales)) - same way applied in set expression

=Sum({<Sales ={">=$(=min(Sales)) <=$(=max({<Product=>}Sales))"}, Product=>}total Sales) - this expression is working fine for one product selection.

but my problem is when user select multiple products not working.

my output should be:

if user select single product or Multiple products should not be change the cumulative value (If any sub filter selections cumulative should not change)

Thanks in Advance!

Re: Cumulative sum should not change based on selection

KNRaju105630 — Sat, 25 Mar 2023 23:11:34 GMT

Experts, any work around backend or UI ?

is there any possibility ?

Re: Cumulative sum should not change based on selection

KNRaju105630 — Mon, 27 Mar 2023 01:43:28 GMT

@tresB , any suggestion please

Re: Cumulative sum should not change based on selection

tresB — Mon, 27 Mar 2023 04:48:07 GMT

sum( aggr( rangesum( above( total sum( {<Product>} Sales),0,RowNo(TOTAL)) ),(Product,(=Sum({<Product>} Sales),Desc))))

Re: Cumulative sum should not change based on selection

KNRaju105630 — Mon, 27 Mar 2023 07:10:57 GMT

Thanks for response!

Same expression while applying to here not working - could you please help, how to apply in below expression

IF(Sum(Sales)<>ABOVE(Sum(Sales)),

RangeSum(Above(Total [Cumulative]), Sum(Sales)),

ABOVE([Cumulative]))

Re: Cumulative sum should not change based on selection

tresB — Mon, 27 Mar 2023 07:12:55 GMT

Please share your complete requirement. I have the qvw now, I will try working on that and see if I can help.

Re: Cumulative sum should not change based on selection

KNRaju105630 — Mon, 27 Mar 2023 08:56:06 GMT

below is my table - I'm calculating cumulative based on sorting and below highlighted two columns (if it is same value cumulative value should be the same).

Here I'm facing issue, if user hit any selection - value should not be the change.

Category	Sub Category	Product	Sales	Cumulative
Books	Qlik	Qlik Sense	30	30
Mobiles	Samsung	S1	25	55
Mobiles	Nokia	N1	12	67
Books	Tableau	Tab	10	77
Mobiles	IPhone	I++	9	86
Books	Spotfire	SF	8	94
Mobiles	Samsung	S2	7	101
Mobiles	Samsung	S3	7	101
Books	Qlik	QlikView	5	106
Mobiles	Nikia	N2	2	108

Re: Cumulative sum should not change based on selection

tresB — Mon, 27 Mar 2023 10:14:39 GMT

Try:

sum(aggr( rangesum( above( total If(Sum({<Product>}Sales)=Above(Total Sum({<Product>}Sales)),0,sum( {<Product>} Sales)),0,RowNo(TOTAL) )),(Product,(=Sum({<Product>} Sales),Desc))))

Re: Cumulative sum should not change based on selection

KNRaju105630 — Wed, 29 Mar 2023 08:40:38 GMT

Awesome work 🙂 - Thank you

Re: Cumulative sum should not change based on selection

KNRaju105630 — Thu, 30 Mar 2023 11:37:06 GMT

HI @tresB ,

is there any possibility like below - I have to cumulative group category level

my output should be like Group Level

Category	Product	Amount	Cumm	Group Level
Books	XAB	30	30	30
Books	XYZ	30	30	30
Books	BCA	30	30	30
Mobiles	I+	30	30	60
Books	ABG	30	30	30
Books	ABC	30	30	30
Mobiles	S1	25	55	85
Mobiles	N1	12	67	97
Books	Tab	10	77	107
Mobiles	I++	9	86	116
Books	SF	8	94	124
Mobiles	S2	7	101	131
Mobiles	S3	7	101	131
Books	QlikView	5	106	136
Mobiles	N2	2	108	138

Please give suggestion

Re: Cumulative sum should not change based on selection

Raghu_MVS — Fri, 31 Mar 2023 10:35:31 GMT

Try:

If(Sum({<Product,[Category ],[Sub Category ]>}Sales)=Above(Total Sum({<Product,[Category ],[Sub Category ]>}Sales1)),
rangesum(above(total sum( {<Product,[Category ],[Sub Category ]>} Sales),1,RowNo(TOTAL))),
rangesum(above(total sum( {<Product,[Category ],[Sub Category ]>} Sales),0,RowNo(TOTAL)) ))