Qlik line chat expression

Question

I have a table like this

Month	Customer Name	Paid	Collected	Actual
1	John Smith	100	1000	10%
2	John Smith	200	1050	19%
3	John Smith	300	1100	27%
1	Kirk Polly	250	1150	22%
2	Kirk Polly	355	1200	30%
3	Kirk Polly	865	1250	69%

The actual column is calculated by Collected divided by Paid. What i want to do is identify the max month so int examples is 3 for both customers and then use the collected amount for the max month and use that value to divide by the paid for each month. So month 1 for John Smith would be 300/1100 month 2 would be 200/1100 Any idea how i can do this in a line chart where i plot the calculated actual using the month and customer name as dimensions.

Thanks in advance!

Ideally directly in the UI i.e as an expression in the line chart — DATAMART, Aug 18 '21 at 15:25

Stefan Stoichev · Answer 1 · 2021-08-18T16:10:14.723

The annotated code below will perform the calculation it in the script. Once reloaded the result table will look like:

Ill check if calculation in the UI is possible

RawData:
Load * inline [
Month,  Customer Name, Paid,    Collected,  Actual
1    ,  John Smith   , 100 ,    1000     ,  10%
2    ,  John Smith   , 200 ,    1050     ,  19%
3    ,  John Smith   , 300 ,    1100     ,  27%
1    ,  Kirk Polly   , 250 ,    1150     ,  22%
2    ,  Kirk Polly   , 355 ,    1200     ,  30%
3    ,  Kirk Polly   , 865 ,    1250     ,  69%
];

// Find the max month for each [Customer Name]
MaxMonth:
Load 
  max(Month)      as Month,
  [Customer Name]
Resident
  RawData
Group By
  [Customer Name]
;

// left join the RawData to get the Collected amount
// for the max month
// name the field MaxMonthCollected
left join 

Load 
  Month,
  [Customer Name],
  Collected      as MaxMonthCollected
Resident
  RawData
;

// join the resulted table (MaxMonth) back to RawData table
// this way agains each row we'll have the max Collected amount
// (based on the [Customer Name]
join (RawData)

Load 
  [Customer Name],
  MaxMonthCollected
Resident 
  MaxMonth
;

// We dont need this table anymore
Drop Table MaxMonth;

// Calculate "Paid / MaxMonthCollected" and name the field "Result"
// at this point the next steps might not be needed
// we have the "MaxMonthCollected" field in "RawData" table so
// we can perform the calculation in UI expression
FinalData:
Load 
  *,
  Paid / MaxMonthCollected as Result
Resident
  RawData
;

Drop Table RawData;
Drop Field MaxMonthCollected; // MaxCollected can be dropped if not needed

UPDATE

Something like the expression below can be used directly in the table.

Bit of a warning. Its a heavy calculation! Running it against large dataset can/will lead to performance issues!

sum(Paid) 
/
aggr( nodistinct
      Sum( {$< Month={"$(=aggr(Max(Month), [Customer Name]))"} > } Collected),
      [Customer Name]
)

Thank you so much! Is there no way to do this directly as an expression? — DATAMART, Aug 18 '21 at 16:01

Qlik line chat expression

1 Answers1