How to use the property control listbox (multiple) in custom expression within a Crossing Table visualisation

Dear anyone,

I am trying to make a calculation (custom expression) in a Cross Table flexible by using the property control List Box (Multiple Select) and somehow I can't get this thing working.
The case is this one:

This is the simplified Cross Table:

Both columns "Avg. Act. Price" and "Avg.FC PricE" are the result of respectively the following two expression:

1) Avg.Act.Price:  Sum(If(([Invoice Quantity]<>0) and ([Fisc.Year Calc.]=2017),[Recalc. Act Sales])) / Sum(If([Fisc.Year Calc.]=2017,[Invoice Quantity])) as [Avg. Act. Price]
2) Avg.FC Price:  Sum(If([FC QTY]<>0,[FC Revenue])) / Sum([FC QTY]) as [Avg. FC Price]

In the custom expression of no.1 you can see that I am trying to limit the calculation of the avg. actual price over the year of 2017. In the current expression this works fine. What I would like is to achieve is that the "2017" becomes flexible by using the property control Listbox (multiple select). In some cases I would like to select more than 1 year  at the same time.

I tried to insert the following property control:

Question:
How should my custom expression look like to achieve this?
The ones I tried did not work.

My recent construcion was this one:
Sum(If([Fisc.Year Calc.]=$map("sum([${Fisc.Year.Listbox}])", ","),[Recalc. Act Sales])) / Sum(If([Fisc.Year Calc.]=$map("sum([${Fisc.Year.Listbox}])",[Invoice Quantity]))) as [Avg. Act. Price]

Could someone please help me out here? So I would like to have a listbox in a text area in which I can slect 1 or more years that should be applicable in the calculation of the column "Avg. Act. Price" in my Crossing Table.

Many thanks in advance!

Kind regards,
Jurjen

(1) Answer

Login