How to use loop in expression instead of script edit

[#1]

I have one dimesion called Category:1, 2,3,4,5

I use one expression wth Set analysis to show: All revenue:

Sum({<Category={'1'}>}Revenue)+Sum({<Category={'2'}>}Revenue)+Sum({<Category={'3'}>}Revenue)+Sum({<Category={'4'}>}Revenue)+Sum({<Category={'5'}>}Revenue)

I know I can put Category into Dimension, but In this time, I have to use hard code instead of using dimension.

Is it possible to use like loop to add them all instead of write 5 expressions?

Thanks.

[#2]

why not use SUM(Revenue) - that way you should get them all.. or just SUM({<Category={'*'}>}Revenue).

[tresesco]

Try:

Sum({<Category={'1','2','3','4','5'}>}Revenue)

[#4]

=IF($(vTodayMonth)  > MONTHNAME(ADDMONTHS(CloseDate, 0)),

sum({<monthcat={'0'}>}Amount_Collected__c)/sum(Final_Price__c),

Avg({<FocastMonthCat={'0'}>}SixMonthAverage))

+

=IF($(vTodayMonth)  > MONTHNAME(ADDMONTHS(CloseDate, 1)),

sum({<monthcat={'1'}>}Amount_Collected__c)/sum(Final_Price__c),

Avg({<FocastMonthCat={'1'}>}SixMonthAverage))

You can see there are 3 places needs use 1,2,3,4,5 to control, that is why I have to use loop

[tresesco]

Without going into your expression, can tell you - no typical loop statement is allowed in qv expression but script.

[#6]

Try Vars to shorten your formula..

$(vCategory) = MONTHNAME(ADDMONTHS(CloseDate, $1)),

sum({<monthcat={$1}>}Amount_Collected__c)/sum(Final_Price__c),

Avg({<FocastMonthCat={$1}>}SixMonthAverage))

Formula:

=if($(vTodayMonth)  > $(vCategory('1')) and so on.. replace '1' with '2' etc.

hope that works

[#7]

sorry i meant

vCategory = MONTHNAME(ADDMONTHS(CloseDate, $1)),

sum({<monthcat={$1}>}Amount_Collected__c)/sum(Final_Price__c),

Avg({<FocastMonthCat={$1}>}SixMonthAverage))