Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks in advance for help.
Vidas
Not sure if that helps:
=sum({<[Debit Code]={6000}>} Value) -sum({<[Credit Code]={6000}>} Value)
This will work for one code only
Not sure if that helps:
=sum({<[Debit Code]={6000}>} Value) -sum({<[Credit Code]={6000}>} Value)
This will work for one code only
How to sum all dimensions which starts with 60, because I have dimensions 6000, 6010, 6011?
Thanks a lot.
Vidas
This is another way(first before I answer your second Question)
t1:
Load * Inline
[
Debit Code, Credit Code , Value
6000, 20140 , 935
6000 , 20140, 125
500 , 6000 , 1200
300 , 20140 , 200
]
;
t2:
load
sum(Value) As val1,
[Debit Code] as Code
Resident t1
group by [Debit Code];
left join(t2)
t3:
load
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
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))
It works perfectly. Thanks a lot.