6 Replies Latest reply on Jun 19, 2012 9:45 AM by ethan.chernin

# create a calculation based on a dimension

I am trying to do the following (i know poorly) and the below is probably not the right start. Final goal is to have Hands on subtracted from Hands off and hands on would remain the same. So in this case the measures would be...

Hands on = 120 min

Hands off = 120 min (e.g., 240-120)

Thoughts?

• ###### 1. Re: create a calculation based on a dimension

If you want to separate them out then two calcs:

Hands Off

if [Hands on vs Hands off]="Hands Off" then [Duration] end

and

Hands On

if [Hands on vs Hands off]="Hands On" then [Duration] end

These can then be summed, averaged, or subtracted from each other (though you would need to use an aggregation as a row is only hands on or hands off: sum([Hands on])-sum([Hands off]) for example

• ###### 2. Re: create a calculation based on a dimension

Thanks Alex. I originally tried that, however I need the Hands on/off to remain a dimension and then one separate measure  to either return the value for "hands on" or if its hands off I need it to create a calculation for "hands off" (hands off - hands on)...

Sorry if this is confusing...

Ethan

• ###### 3. Re: create a calculation based on a dimension

Yep, no idea what you want. How would you write it in Excel?

• ###### 4. Re: create a calculation based on a dimension

Sorry... I need to do the following...

• ###### 5. Re: create a calculation based on a dimension

Not as straightforward as you would think as you need to access the sum of all the other buckets from the null group. I've used a table calc - there may be other/better ways of doing this.

if isnull(attr([CareGiver Buckets])) then sum([Duration Minutes])-

window_sum(sum(if not isnull([CareGiver Buckets]) then [Duration Minutes] else 0 end)) else sum([Duration Minutes]) end

• ###### 6. Re: create a calculation based on a dimension

That's it, thanks!