I know why my data's disappearing, but what can I do about it?

[justin_morley]

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

[justin_morley]

In case anyone's interested, I'm using the method outlined here:

Multiple expressions in an expression field in a pivot table

[MK_QSL]

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)))))))

[settu_periasamy]

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)

))

[MK_QSL]

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)))

[rustyfishbones]

Hi Justin,

Do you want to Dynamically change the expressions?

[justin_morley]

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

[justin_morley]

Hi Alan,

Not quite sure what you mean by dynamically change the expressions. The selections can certainly change.

Thanks

Justin

[justin_morley]

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

[justin_morley]

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