topic Re: Shortening a expression in QlikView

Shortening a expression

Zaga_69 — Sat, 16 Nov 2024 03:35:41 GMT

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

)

Re: Shortening a expression

marcus_sommer — Tue, 14 May 2019 11:34:18 GMT

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

Re: Shortening a expression

Zaga_69 — Tue, 14 May 2019 11:50:57 GMT

Hi Marcus,

Thanks!

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

Re: Shortening a expression

marcus_sommer — Tue, 14 May 2019 13:48:37 GMT

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

Re: Shortening a expression

Zaga_69 — Tue, 14 May 2019 21:21:24 GMT

Thanks Marcus.

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

Thanks

Re: Shortening a expression

marcus_sommer — Wed, 15 May 2019 06:27:37 GMT

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

Re: Shortening a expression

anushree1 — Wed, 15 May 2019 07:38:52 GMT

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