5 Replies Latest reply: Jun 15, 2017 8:30 AM by thomas le gall

# set analysis : sum total with criteria

Dear all,

I have a table with the following informations :

- SITE NAME

- WORKSHOP

- TYPE OF PRODUCT

- VOLUME

The target shoud be to have on dimension : site name, workshop and type of product and to calculate :

- the volume => ok it's easy : SUM(Volume)

- the ratio : volume of final product of the site (i.e : TYPE OF PRODUCT=FINAL PRODUCT, in my example : 56 130) / volume

=> I have used this formula to calculate the total volume of final product by site :

sum(total<[SITE NAME]>{<[TYPE OF PRODUCT]={'FINAL PRODUCT'},WORKSHOP=>}  [VOLUME])

and... it works !

but... if I do a filter on the WORKSHOP dimension, workshops with final product are always displayed

for example : with a filter on the workshop called 'MINE' only :

How can I change this formula to display only workshop that I've selected ?

Thank you very much for your help !

Regards,

• ###### Re: set analysis : sum total with criteria

Any ideas ?

• ###### Re: set analysis : sum total with criteria

I don't think so whether you require any changes over expressions. It filters default to selected one only. Would you able to provide sample

• ###### Re: set analysis : sum total with criteria

Hi,

Not sure but you can try this.

sum(total<[SITE NAME],WORKSHOP>{<[TYPE OF PRODUCT]={'FINAL PRODUCT'}>}  [VOLUME])

Regards,

Kaushik Solanki

• ###### Re: set analysis : sum total with criteria

Hello, Julien!

The First exp is sum(val)

The Second exp is sum(total {<[TYPE OF PRODUCT]={'FINAL PRODUCT'}>}  [VOLUME])

The Third exp is column(2)/column(1).

Worked on my example. If it won't - please, share a sample of your data.

• ###### Re: set analysis : sum total with criteria

Hi Julien,

Hehe tricky,

Try this :

if(WORKSHOP=WORKSHOP,sum(total<[SITE NAME]>{<[TYPE OF PRODUCT]={'FINAL PRODUCT'},WORKSHOP>}  [VOLUME]))