topic Re: Dynamic set analysis dimension in QlikView

Dynamic set analysis dimension

triekong — Sat, 16 Nov 2024 20:48:56 GMT

i have 3 Fiscal year flag fields in my dataset, for 1 Amount Field. 'FiscalYear1Amount', 'FiscalYear2Amount' and 'FiscalYear3Amount' respectively. when 'FiscalYear1Amount'=1, it filters the 'Amount' field to only the amount for fiscal year 1. and so forth.

My current sum of Amount for the current fiscal year is: sum({<FiscalYear1Amount={'1'}>} amount).

i want to be able to change the dimension dynamically, to maybe FiscalYear2Amount or FiscalYear3Amount, using a variable $(v_FiscalYear), which should pass the field for the selected fiscal year..

sum({<$(v_FiscalYear)={'1'}>} amount)

but my syntax seems off. please guide.thanks!

(SN: All the fiscal year flag fields have values of either 1 or 0)

Re: Dynamic set analysis dimension

sunny_talwar — Wed, 22 May 2019 19:40:59 GMT

Try one of these

Sum({<$(='[' & $(v_FiscalYear) & ']') = {'1'}>} amount)

Sum({<$(='[' & v_FiscalYear & ']') = {'1'}>} amount)

Ignore the fact that syntax editor doesn't like the expression... just click okay to see if you get the correct result or not

Re: Dynamic set analysis dimension

triekong — Wed, 22 May 2019 20:31:23 GMT

thanks @sunny_talwar ! big help

Quick note to anyone else, when passing the variable, make sure the field name is in single quotes. ''

for example: when i pass FiscalYear1Amount, i make sure i include it as 'FiscalYear1Amount'