pick match with several ValueLists

felixtheu — Mon, 12 Aug 2019 16:05:41 GMT

Hi all,

I have a problem where I can not get further and have found no answer in the threads:
I want to create a pivot with two dimensions and several value functions.

Dimension "CAGR Global" =ValueList('CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)')

Dimension "CAGR Kontinent" =ValueList('Europa', 'Nordamerika ', 'Asien', 'Süd- und Mittelamerika', 'Afrika', 'Australien und Ozeanien')

I want to report in this pivot either the "CAGR "Values for dimension "CAGR Global" or "CAGR Kontinent"

My problem is to find a correct pick-match function, which consider the "correct" Valuelist. Currently, the values of dimension "CAGR Global" are equal to the values of dimension "CAGR Kontinent" by using this function

=pick(match(ValueList('CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)'),'CAGR(2005, 2010)', 'CAGR(2010, 2015)', 'CAGR(2015, 2018)'),
10,
13,
18)

How can i consider the second valuelist in the measures?

Thank you very much for your help 🙂

BR,

Felix

Re: pick match with several ValueLists

marcus_sommer — Tue, 13 Aug 2019 06:21:19 GMT

I don't want to say that's not possible but if it would be quite difficult because between both calculated dimensions is no relationship which means they create a cartesian product. I suggest to transfer the logic to the script and create there your data-structure, for example with:

t: load * inline [
CAGR Global
"CAGR(2005, 2010)"
"CAGR(2010, 2015)"
...
];

join(t) load * inline [
CAGR Kontinent
Europa
Nordamerika
...
];

Afterwards it are native dimensions and you could use pick(match()) to react on them. Whereby it might be even more easier if these data are connected to your real datamodel ...

- Marcus

topic Re: pick match with several ValueLists in App Development

pick match with several ValueLists

Re: pick match with several ValueLists