6 Replies Latest reply: May 12, 2018 1:35 PM by bhasker kumar

# sum function

Hello friend

I have the below two tables:

1/ Facts Table:

collateral_idcollateral_amount
1\$200
2\$200
3\$200

2/ Ageing Table

bucketcollateral_idage
June/2017130
June/2017255
June/2017360
July/2017125

In the visuals I want to have the following output (by bucket)

bucketAmountAge 0-30Age 30-60
June/2017200+200+200=600200200
Jul/20172002000

for the amount: sum(collateral_amount)

for the Age: sum({<age={">0<30"}> collateral_amount}

Problem & question:

the result is correct only when i select the dimenstion in the output table. the result is incorrect if i do not select the dimension

So when i select June/2017, the measure "Amount, Age0-30 and Age 30-60" and recalculated and they become correct.

How can i obtain the correct result without the need to select the dimension ?

(note that it is not possible to connect merge the two table in a single one)

Thanks

• ###### Re: sum function

Hi Bassam,

Please find attached application on the data you shared. Hope this helps.

Regards,

Akshaya

• ###### Re: sum function

hi Akshaya,

Tk you

how can i import qlikview app to qlik sense ?

• ###### Re: sum function

Hi Bassam,

You can use the logic used in the app to your Qliksense.

I have created a extra column in data using class function and using it in the expression.

Script :

collateral_id, collateral_amount

1, 200

2, 200

3, 200

];

T:

bucket, collateral_id, age

June/2017, 1, 30

June/2017, 2, 55

June/2017, 3, 60

July/2017, 1, 25

];

NoConcatenate

TF:

LOAD *,class(age,31,'Age') as AgeGroup Resident T;

Drop Table T;

Front End

Expression - sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 0 - 30

- sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 31 - 62

Hope this helps.

Regards,

Akshaya

• ###### Re: sum function

Hi,

Please find below script and Expression which was used to calculate the desired result.

Note: I haven't linked and marge the two tables.

FactsTable:

collateral_id1, llateral_amount

1,\$200

2,\$200

3,200

];

ageing_table:

bucket,collateral_id,age

June/2017,1,30

June/2017,2,55

June/2017,3,60

July/2017,1,25

];

2. On Chart Expression i have used calculated dimension in order to populate the bucket

Dimension :=if(match(collateral_id,collateral_id1),bucket)

Expression: Age 0-30 :  sum({< age = {">0 <= 30 "}>}llateral_amount)

Age 30 -60: sum({< age = {">30 <=60 "}>}llateral_amount)

Regards,

• ###### Re: sum function

Can you explain what is the rationale ? what was wrong and how you fixed it ?

Is this the only way?

I would have like to keep the two table connected through collateral_id

thanks

• ###### Re: sum function

Hi,

You can connect both the table through collateral_id.

I haven't faced any issue just used in line load and required expression, there may be issue with the data formatting connected dimension or used dimensions in your chart.

for the age bucking you can either use front expression that i have used or in back-end you can use Class function.

i have used calculated dimensions: if(match(collateral_id,collateral_id1), bucket) , match function return true when first table collateral_id values matched collateral_id1 then it will return the corresponding bucket  in dimension.