Question - Total Sum based on Hidden Dimension

Tue, 09 Jun 2015 22:00:59 GMT

Hi Guys,

Need help on a table that I'm trying to create. I'm going to show it in a pictorial view for better understanding.

Table 1 :

Customer	Part	Forecast	Revenue	Impact
		22	30	12
CUSTOMER A	Part A	0	18	0
CUSTOMER A	Part B	10	12	12
CUSTOMER A	Part C	12	0	0

2 dimensions : Customer and Part - Table 1 is showing specifically for Customer A

Expressions : Forecast = SUM(Forecast)

Revenue = SUM(Revenue)

Impact = IF(Forecast>0, Revenue, 0)

And the total on top. Forecast = 22

Revenue = 30

Impact = 12

This is what we have now, and this is fine.

What we are trying to achieve is :

Table 2:

Customer	Forecast	Revenue	Impact
CUSTOMER A	22	30	12
CUSTOMER B	30	45	21
CUSTOMER C	43	64	33

This table is basically a summary without the part.

Forecast and Revenue columns is fairly easy , It would use the same formula as the above table,

Problem is with the Impact column, I need it to follow the same formula but at the part level .

If I were to use the same formula, but not at the part level, I would get 30 based on the formula IF(Forecast>0, Revenue, 0) which is wrong.

I would like the formula to sum up at the part level without showing the part .

Can this be done ?

Is this got to do with AGGR function ? I tried but failed.

Customer B and Customer C is just an example.

Thanks in advance for the help.

Regards,

Shan Raj.

Re: Question - Total Sum based on Hidden Dimension

petter — Tue, 09 Jun 2015 22:12:46 GMT

Yes the Aggr() function is often the solution if you remove a dimension then that will have to be compensated for by an Aggr().

For example like this ... I think:

Forecast = Sum( Aggr( Sum( Forecast ) , Part )

swuehl — Tue, 09 Jun 2015 22:13:58 GMT

Yes, advanced aggregation should do what you want. For impact, try

=sum( aggr( if(sum(Forecast)> 0, sum(Revenue)), Customer, Part))