8 Replies Latest reply on Jul 14, 2016 8:00 AM by swaroop.gantela

Sum accross multiple dimensions in Table

Hello everyone,

This may be a simple question but I can't get the solution.... I need to reorganise the following data

into the following table

I will certainly need to Sum Rate 1 and 4 as well,  and 3 and 4, etc, and it seems that Groups are not applicable for that.

What would be the solution ? Create a calculated field ? Bu It can't find out what the syntax is for the calculation.

KR

Béa

• 1. Re: Sum accross multiple dimensions in Table

Beatrice,

Please see if the attached could be a first step for you.

I used Level of Detail calculations to get the counts.

For Rate 1:

{ FIXED [Source]:SUM(IF [Rate]=1 THEN 1 ELSE 0 END )}

Meaning if anywhere in a particular Source there is a Rate1, count it

and fix the sum for the whole source.

I also used that count in getting the Amount sum:

IF [CountRate1]>0

THEN { FIXED [Source]:SUM([Amount])}

ELSE 0

END

Using this scheme, I needed to make a separate

calculation for each combination.

To get the Count for Rate1&3:

IF [CountRate1]>0 AND [CountRate3]>0

THEN { FIXED [Source]:COUNT([Rate])}

ELSE 0

END

1 of 1 people found this helpful
• 2. Re: Sum accross multiple dimensions in Table

Hi Swaroop,

Thanks a lot for your help !

The count works well !!

but I don't get the correct Amount sum with the formula proposed and I am not sure what the amount I get relates to...

• 3. Re: Sum accross multiple dimensions in Table

Hello Again,

Actually, the formula for the count is not working. It gets a correct total but not a correct total by line (source).

I changed to

SUM(IF [Rate]=1 THEN 1 ELSE 0 END ) and now it's working.

What I still can't get is the amount.

Thanks a lot

• 4. Re: Sum accross multiple dimensions in Table

Beatrice,

Would need some clarification of what you seek for the Amount.

For the Rank 1 Amount, are you seeking:

any source that has a Rank 1, add up all of their Amounts

(regardless of the Rank).

So in your above example, Source 1 does have amongst

its entries a Rank1, so would you want to sum the amounts of

Lines 1,3, and 7 (even though line 7 has a rank of 3)

which would total 800, as you have in the example?

And then for Rank 1,3 Amount,

add up only the amounts for those Sources that have both

a Rank1 and a Rank3?

1 of 1 people found this helpful
• 5. Re: Sum accross multiple dimensions in Table

Hi again Swaroop,

Thanks so much :-) And a big thank you because i realize i made a mystake yesterday in my total, (was too late for me:-) My bad !  This is what I am trying to achieve :

• 6. Re: Sum accross multiple dimensions in Table

Beatrice,

That does make the sums more straightforward.

Should be just like the counts:

SUM(IF [Rate]=1 THEN [Amount] ELSE 0 END)

And for 1 and 3:

SUM(IF ([Rate]=1 OR [Rate]=3) THEN [Amount] ELSE 0 END)

2 of 2 people found this helpful
• 7. Re: Sum accross multiple dimensions in Table

Thanks Swaroop, that works perfectly fine !

Have a great day,

Kind regards

• 8. Re: Sum accross multiple dimensions in Table

Beatrice,