Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Im Looking for a quicker way to filter a measure that is derived from a variable. rather than recreate the same measure based on filters.
i have a measure in a master item eg $(vActual_FY) that come from a variable
the calculation in this variable is
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12} >}[value_qty])/1000
now i want to further filter this measure based on a value that exists in a dimension in certain circumstances
PNL_long_Header = {'IAT'}
i guess i can create a new measure with the added filter, like below
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12},PNL_long_Header = {'IAT'} >}[value_qty])/1000
but i am hoping there is an easier way without replicating , where i can just call the Variable $(vActual_FY) and set the additional filter
$(vActual_FY)
something like
aggr($(vActual_FY),PNL_long_Header = {'IAT'})
Any Ideas
Hi,
You can use argument inside a variable
It will look like this: $(vActual_FY(argument))
Here is how you should change your measure (note the $1 at the end):
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12} $1 >}[value_qty])/1000
If you want to trigger original value:
$(vActual_FY(''))
If you want the new value:
$(vActual_FY('PNL_long_Header = {'IAT'}'))
(you may need to escape the ' mark though)
I hope that helps!
Kind regards,
S.T.
Hi,
You can use argument inside a variable
It will look like this: $(vActual_FY(argument))
Here is how you should change your measure (note the $1 at the end):
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12} $1 >}[value_qty])/1000
If you want to trigger original value:
$(vActual_FY(''))
If you want the new value:
$(vActual_FY('PNL_long_Header = {'IAT'}'))
(you may need to escape the ' mark though)
I hope that helps!
Kind regards,
S.T.
Create your variable like below. Replace whatever modifier you want to add with $1,$2.....
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12},$1 >}[value_qty])/1000
Then you can pass the parameter within variable like below
=$(vActual_FY(PNL_long_Header = {'IAT'}))
thanks this works perfect i just added the comma after the argument
sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={12} , $1 >}[value_qty])/1000
and called it via
$(vActual_FY(''))
and with a filter
$(vActual_FY(PNL_long_Header = {'IAT'}))
many thanks