Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bassofrem
New Contributor

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

6 Replies
adityaakshaya
Contributor III

Re: sum function

Hi Bassam,

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

Regards,

Akshaya

bhasker_smu
Contributor II

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.

Data Load Script:

FactsTable:

Load * inline [

collateral_id1, llateral_amount

1,$200

2,$200

3,200

];

ageing_table:

Load * inline [

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)

chart.PNG

if you found this helpful please mark helpful or correct.

Regards,

Bhasker Kumar

bassofrem
New Contributor

Re: sum function

hi Akshaya,

Tk you

how can i import qlikview app to qlik sense ?

bassofrem
New Contributor

Re: sum function

Thank you bhasker it worked

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

adityaakshaya
Contributor III

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 :

LOAD * INLINE [

  collateral_id, collateral_amount

    1, 200

    2, 200

    3, 200

];

T:

Load * INLINE [

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

bhasker_smu
Contributor II

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.

if you found this helpful, please mark correct or helpful.

Regards,

Bhasker Kumar

Community Browser