Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
)
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
Hi Marcus,
Thanks!
In your first proposal, I should create 12 variables, right? Each one for each period?
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
Thanks Marcus.
However, I do not have any idea how to implement the parameter. Can you please give me a hint?
Thanks
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
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