Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

Thanks in advance for help.

Vidas

1 Solution

Accepted Solutions

Re: Dimension calculation

Not sure if that helps:

14-Apr-15 2-45-15 PM.jpg

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

This will work for one code only

5 Replies

Re: Dimension calculation

Not sure if that helps:

14-Apr-15 2-45-15 PM.jpg

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

This will work for one code only

Not applicable

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

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

14-Apr-15 2-56-03 PM.jpg

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))

Not applicable

Re: Dimension calculation

It works perfectly. Thanks a lot.