Set analysis use value of another expression

[#1]

Hello,

I have a table looking like this:

AccountNo	Formula	Amount
1	">=100000<=199999"	To be calculated (700)
100000		100
110000		100
120000		500
2	">=200000<=299999"	To be calculated (550)
200000		200
200009		300
210000		50

I want to calculate the amount for headers 1 and 2 using set analysis. I've made the expression 'Formula' which contains a formula I can use to filter the correct account numbers for the total.

If I use      Sum({$<AccountNo = {">=100000<=199999"}>}TOTAL Amount)      I get a correct result for AccountNo 1. But then I have to make a formula for every total account there is..

Is there any way I could use the value of another expression in a set analysis formula to calculate another expression?

I have tried things like Sum({$<AccountNo = Formula>}TOTAL Amount) but that didn't work.

[Anonymous]

hi did u try

Sum({1<AccountNo = {">=100000<=199999"}>}TOTAL Amount)  

[Gysbert_Wassenaar · Accepted solution]

You can't use set analysis expressions here since you want different sets for different rows. You'll have to create accountgroups in some way. Attached is an example that with three different ways. Look at the calculated dimensions to see how the groups are created. You could also create the groups in the script.

[#4]

Thx for your reply,

I don't want to use ">=100000<=199999" in my set analysis, I want to use the expression 'Formula' somehow