Shortening a expression

[Zaga_69]

Hi All,

I create the following expression in a barchart, however i think there is a room for improvement. Any idea how to shorter it?

=PICK(MATCH(ValueList('BUD '&Year, 'P01','P02','P03','P04','P05','P06','P07','P08','P09','P10','P11','P12' ), 'BUD '&Year, 'P01','P02','P03','P04','P05','P06','P07','P08','P09','P10','P11','P12' )
,(SUM({< $(_Filter_Balance), Period, Scenario={BUD}>} Working_Capital)/SUM({< $(_Filter_Balance), Period, Scenario={BUD}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={1}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={1}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={2}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={2}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={3}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={3}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={4}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={4}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={5}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={5}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={6}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={6}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={7}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={7}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={8}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={8}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={9}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={9}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={10}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={10}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={11}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={11}>} Product_Sales))*30
,(SUM({< $(_Filter_Balance), Period={12}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={12}>} Product_Sales))*30




)

[marcus_sommer]

You could use a variable with parameters for your redundant expression, like:

variable: e
SUM({< $(_Filter_Balance), Period={$1}>} Working_Capital)/SUM({< $(_Filter_Balance), Period={$1}>} Product_Sales)*30

and then you could call them with something like:

$(e(1))

Beside this I would rather try to avoid a valuelist-construction and using either an island-table for it or even more likely I would use an extra period-field (duplicate from the origin one) and adding to it an additionally TOTAL value - and after it your expression could look like:

if(ExtraPeriod = 'TOTAL',
   SUM({< $(_Filter_Balance), Period >} Working_Capital)/SUM({< $(_Filter_Balance), Period >} Product_Sales)*30,
   SUM({< $(_Filter_Balance)>} Working_Capital)/SUM({< $(_Filter_Balance)>} Product_Sales)*30)

- Marcus

[Zaga_69]

Hi Marcus,

Thanks! 

In your first proposal, I should create 12 variables, right? Each one for each period?

[marcus_sommer]

No, one variable for all periods and using the parameter to change them, like:

pick(match(F, 1,2,3, ...), $(e(1)), $(e(2)), $(e(3)), ...)

- Marcus

[Zaga_69]

Thanks Marcus.

However, I do not have any idea how to implement the parameter. Can you please give me a hint?

Thanks

[marcus_sommer]

In this case are the (bold) numbers the parameter:

pick(match(F, 1,2,3, ...), $(e(1)), $(e(2)), $(e(3)), ...)

In general it's the same like your expression-structure because match(F, 1,2,3, ...) means the same like your match(valuelist()).

- Marcus

[anushree1]

Please read through https://community.qlik.com/t5/QlikView-Documents/Qlikview-Parameterised-Variables/ta-p/1494184.

It will give you an understanding of what Parameterised variables are and how you could use them