Expression

[Andy_P]

Hi all,

I have the expression below where the filter is not being applied on the last sum, the sum in bold works fine, I expected the last sum to keep the ACCODE filter but the division ignores it. If I change the / to a + then the filter works, I don’t understand why this doesn’t work. Can you help please?

If( LineItem = 'Average COS per kg (£)', Num((( Sum({ <NCODE={ '20050', '20052', '20051' }> } NTURNOVER_N3)

/

Sum({<DET_PERIODNUMBR={'3'}, ACCODE>} QTY * STK_S_WEIGHT) // ACCODE filter ignored here

*

Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT))

/

Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT))

/

1000, '#,##0.00')

Thanks

[Oleg_Troyansky]

Hi @Andy_P ,

I don't see anything wrong in the syntax, and I don't see any reason why the same Set Analysis would work when aggregations are summed up versus divided one by another. There has to be something else in the data, or in the chart structure, that makes it appear as if it's not working in one case, and working in another case.

Cheers,

Oleg Troyansky

[rwunderlich]

if you multiply by something and then divide by the same thing isn't that the same as it not being there at all?

*

Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT))

/

Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT))

[Andy_P]

Good morning, 

Sorry I didn’t explain very well, the expression in bold is a total, then I need to divide that total by Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT)) / 1000 

I get what you are saying, I wish it was that easy!!

Thanks

[Chanty4u]

Try this 

If(LineItem = 'Average COS per kg (£)',

   Num(

       (

         (

           Sum({<NCODE={'20050','20052','20051'}>} NTURNOVER_N3)

           /

           ( Sum({<DET_PERIODNUMBR={'3'}, ACCODE>} QTY * STK_S_WEIGHT) ) 

         )

         *

         ( Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT) ) 

       )

       /

       ( Sum({<DET_PERIODNUMBR={'3'}>} QTY * STK_S_WEIGHT) ) 

     * 1000

   ,'#,##0.00')

)