4 Replies Latest reply on Jun 3, 2013 1:25 PM by Andrew Ball

calculated field for data at granular level

Hi All

I have this fieild which has value like:

Event Name       Amount

I                            5

E                           6

R                          2

I need to sum up the amount according to this rule i.e when event is I it should 5+6+2 when it is E it should be 6+2 and when it is R it should be 2.

Please suggest me the case or if statement for this.

Thanks Sid

• 1. Re: calculated field for data at granular level

Hi Siddhartha,

Are you just doing a reverse running sum? Or is E always always equal to E+R ?

The other thing is how do you want this to appear? - is it a whole long list of E, I and R and you want the full sum against each, or just three figures at a top level?

Apologies for lots of questions, but there are many ways of solving your problem, and the best way depends on exactly what you want out at the end.

Andrew

• 2. Re: calculated field for data at granular level

Hi Andrew

I am not doing the reverse running sum.

Actually its a whole long list of I, E and R. To explain it more its like

I     5

E   6

R   2

I    1

E  1

R  1

So its like for "I" i should get the entire sum i.e. 5+6+2+1+1+1. For E its E+R and for R its only R.

i Hope this helps.

Thanks

Sid

• 3. Re: calculated field for data at granular level

Hi Sid,

You could do this using 3 new measures (well, you only need 2, but I'd create 3 for completeness):

Measure "I"

[Amount]

Use SUM(I) in the view

Measure "E"

IF [Event Name] = "I" THEN null ELSE [Amount] END

Use SUM(E) in the view

Measure "R"

IF [Event Name] = "R" THEN [Amount] END

Use SUM(R) in the view

This method is assuming you don't want to see the sum of all I, E and R against every I. Let me know if this is not the case.

Andrew

• 4. Re: calculated field for data at granular level

Thanks Andrew. It was of great help.