Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It's taken me ages to work out what my problem is, but I've managed to exemplify it very simply in the attached app.
If you look at the app, Table 1 shows no data in column k, I think because my field "Condition" is selected. The set expression doesn't do what I expected it to - to look at all the data, not just the current selection, and I think this is because the Dimension comes from an unconnected data island (I previously implemented this as a ValueList, which gives the same result).
The expression needs to be an if statement like this as the conditions can get pretty complex:
if(RowTitle='A',
Sum({<Condition={Ac}>}Value),
if(RowTitle='B',
Sum({$<Condition={Bc}>}Value)
))
It's essentially a pretty well trodden methodology for maintaining multiple expressions in different rows within a single table, which I borrowed from elsewhere.
Bearing this in mind, any ideas how I can force all the data to appear in column k?
Apologies for not being able to explain this very well, I knew I'd have to show rather than tell!
Cheers,
Justin
In case anyone's interested, I'm using the method outlined here:
Multiple expressions in an expression field in a pivot table
if(RowTitle='A' and Param = 'i',
Sum({<Condition={'Ac'}, Param = {'i'}>}Value),
if(RowTitle='A' and Param = 'j',
Sum({<Condition={'Ac'}, Param = {'j'}>}Value),
if(RowTitle='A' and Param = 'k',
Sum({<Condition={'Ac'}, Param = {'k'}>}Value),
if(RowTitle='B' and Param = 'i',
Sum({<Condition={'Bc'}, Param = {'i'}>}Value),
if(RowTitle='B' and Param = 'j',
Sum({<Condition={'Bc'}, Param = {'j'}>}Value),
if(RowTitle='B' and Param = 'k',
Sum({<Condition={'Bc'}, Param = {'k'}>}Value)))))))
Hi,
I think you missed single quote in the set expression
May be like this
if(RowTitle='A',
Sum({<Condition={'Ac'}>}Value),
if(RowTitle='B',
Sum({$<Condition={'Bc'}>}Value)
))
Or
if(RowTitle='A' and Match(Param,'i','j','k'),
Sum({<Condition={'Ac'}, Param = {'i','j','k'}>}Value),
if(RowTitle='B' and Match(Param,'i','j','k'),
Sum({<Condition={'Bc'}, Param = {'i','j','k'}>}Value)))
Hi Justin,
Do you want to Dynamically change the expressions?
Hi Manish,
Thanks for the answer - both look plausible, but in my test application, column k still comes out containing nulls rather than the actual value for Bc
Thanks,
Justin
Hi Alan,
Not quite sure what you mean by dynamically change the expressions. The selections can certainly change.
Thanks
Justin
Hi settu,
Thanks for the reply.
The quotes are optional when the set expression restriction value has no spaces, so adding them makes no difference, sorry to say.
Justin
Hi Manish,
Just to add - if you've got it working using the above expressions, please could you share your app with me, so I can see why mine isn't working.
Thanks,
Justin