Pick match + ignore a field selection problem

Thu, 31 Jul 2014 18:06:34 GMT

Hi community,

I'm trying to write a chart (/straight table / pivot table) expression that uses pick(match()) to select the value displayed. I also need to ignore all selections in fields Month, Year and Quarter as this would be a chart that shows Field1 progression over time.

So, I have in the dimensions list:

QuarterName

And my expression is:

pick(match(Field1, 'val1', 'val2', 'val3'),

sum({<Month=, Year=, QuarterName=>}1),

sum({<Month=, Year=, QuarterName=>}10),

sum({<Month=, Year=, QuarterName=>}100))

At the moment the chart is calculated, only one possible value for Field1 will be available (this is forced by calculation condition).

I would expect my chart to show a line on all quarters of all years but if I make a selection in QuarterName (or year or month), it doesn't ignore it... unfortunately, it does reflect the field selection!

Can anyone point me in the right direction?

Thank you very much!

Best Regards,

Marina C.

Re: Pick match + ignore a field selection problem

Fri, 01 Aug 2014 09:20:28 GMT

Ok,

It was actually really simple and posting this question helped me get there: I was missing the Set Analysis on evaluating Field1 values so it too would ignore selections on Month, Year and QuarterName.

So the expression should be:

pick(match(Only({<Month=, Year=, QuarterName=>}Field1), 'val1', 'val2', 'val3'),