Is this possible in Set Analysis

[shane_spencer]

Hi All - In the attached I'm trying to populate the Final Value for Box 519 with the Count of the FK_FINANCIALDATA2 of Box 538. The trouble is the Ref: field is a Dimension so when I use Set Analysis to do a count of Box 538 it only shows in the row associated with Box 538 not all rows / Box 519. Is it possible to do this in the chart or do I need to sort it out in the load?

[YoussefBelloum]

Hi Shane,

on the Final value expression try to add the ELSE part to set an expression there.

and for the last COUNT VAT expression, try to add an IF statement to be able to calculate the rest.

Let me know !

[shane_spencer]

Hi Youssef - I'm not sure that I follow what you're suggesting. By the way the Count of VAT on Sales column will be removed if/when I can get that figure of 641 in to Final Value for Box 519. It was just a way of braking down the formula.

[YoussefBelloum]

The expression you put is: Count( {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

So if the BREF <> it will be 0.

I do not know if I understand your need...

[shane_spencer]

Somehow I want to get the Total Count of 641 for the row Box 538 in the cell for Final Value for Box 519.

So essentially instead of 641 being only in Count of VAT for Sales for the row called Box 538 I want it to be in all rows. So it's not dependent on the dimension. If I can work out a formula for that I can then use it as part of the If statement in the Final Value column.

[Frank_Hartmann · Accepted solution]

try this:

if((If(Map_BoxRef_clo2='Box 519'

     ,Count( {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

          ,if(Map_BoxRef_clo2=9999,'-',sum(Box_Value)) )=0)

               ,Count(TOTAL {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

                    ,sum(Box_Value))

or this:

If(BREF='Box 519'

     ,Count( TOTAL{$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

          ,if(Map_BoxRef_clo2=9999,'-',sum(Box_Value)))

[shane_spencer]

Hi Frank - the second formula worked a treat!