sum function

[Anonymous]

Hello friend

I have the below two tables:

1/ Facts Table:

collateral_id	collateral_amount
1	$200
2	$200
3	$200

2/ Ageing Table

bucket	collateral_id	age
June/2017	1	30
June/2017	2	55
June/2017	3	60
July/2017	1	25

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

bucket	Amount	Age 0-30	Age 30-60
June/2017	200+200+200=600	200	200
Jul/2017	200	200	0

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

[adityaakshaya]

Hi Bassam,

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

Regards,

Akshaya

[bhaskar_sm]

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)

if you found this helpful please mark helpful or correct.

Regards,

Bhasker Kumar

[Anonymous]

hi Akshaya,

Tk you

how can i import qlikview app to qlik sense ?

[Anonymous]

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]

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

[bhaskar_sm]

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