topic Re: How to use loop in expression instead of script edit in QlikView

How to use loop in expression instead of script edit

Wed, 04 Sep 2013 05:35:49 GMT

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.

Re: How to use loop in expression instead of script edit

Wed, 04 Sep 2013 05:57:02 GMT

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

Re: How to use loop in expression instead of script edit

tresB — Wed, 04 Sep 2013 05:57:52 GMT

Try:

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

Re: How to use loop in expression instead of script edit

Wed, 04 Sep 2013 06:09:52 GMT

=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

Re: How to use loop in expression instead of script edit

tresB — Wed, 04 Sep 2013 07:03:09 GMT

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

Re: How to use loop in expression instead of script edit

Wed, 04 Sep 2013 07:46:29 GMT

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

Re: How to use loop in expression instead of script edit

Wed, 04 Sep 2013 07:48:45 GMT

sorry i meant

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

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

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