5 Replies Latest reply: Apr 15, 2015 4:40 AM by Vidas Rojus

# Dimension calculation

Hi,  need help with calculation of dimension

I have a data (see example below) and I need to make calculation for Debit code.

I create dimension as Debit code and measure as Sum(Value).

Result (I need for my calculation for Debit code 6000) 935+125-1200=-140.

In other words value of Debit code dimension minus value of Credit code dimension.

Debit code      Credit code      Value

6000               20140               935

6000               20140               125

500                 6000               1200

300                 20140               200

Vidas

• ###### Re: Dimension calculation

Not sure if that helps:

=sum({<[Debit Code]={6000}>} Value) -sum({<[Credit Code]={6000}>} Value)

This will work for one code only

• ###### Re: Dimension calculation

How to sum all dimensions which starts with 60, because I have dimensions 6000, 6010, 6011?

Thanks a lot.

Vidas

• ###### Re: Dimension calculation

t1:

[

Debit Code, Credit Code , Value

6000,               20140   ,            935

6000 ,              20140,               125

500   ,              6000 ,              1200

300    ,             20140  ,             200

]

;

t2:

sum(Value) As val1,

[Debit Code] as Code

Resident t1

group by [Debit Code];

left join(t2)

t3:

sum(Value) As val2,

[Credit Code] as Code

Resident t1

group by [Credit Code];

drop table t1;

Create straight Table:

Dimension :Code

Expression :val1-val2

• ###### Re: Dimension calculation

Do you mean dimension or rows within dimension?

Try like this:

=sum(if(left([Debit Code],2)=60,Value))-sum(if(left([Credit Code],2)=60,Value))

• ###### Re: Dimension calculation

It works perfectly. Thanks a lot.