KPI value from calculated field of straight table

[Anonymous]

Hello Qlik Community!

I am just new to the environment and I am having hard time with this one:

I created a straight table with filters via some expression in dimension 'Service_Order':

aggr(only({<Service_Type = {'EM'}> - <Repair_Code = {'40','41','42','43'}> - <SO_Exemption -= {'Included'}>} Service_Order),Service_Order)

I excluded some rows based on values of columns not shown in the table.

'Repeated_Calls' column is a calculated field with expression:

if(Serial_No=below(total Serial_No),if(below(total num(Entry_Date))-num(Entry_Date)<=7,1,0),0)

above expression means, if the Serial_No is the same in row1&row2 and date row2-row1 is less than 7, it will return 1, otherwise 0.

Service_Order | Client | Serial_No | Entry_Date | Repeated_Calls

100000 | ABC | ABC123 | 07/01/2018 | 0

100001 | BCD | BCD234 | 07/09/2018 | 0

100004 | BCD | BCD234 | 07/13/2018 | 1

100006 | BCD | BCD234 | 07/19/2018 | 1

100008 | DEF | DEF345 | 07/02/2018 | 0

100011 | DEF | DEF345 | 07/09/2018 | 1

Now, my question is how can I put the total count of 1 from 'Repeated_Calls' column to a KPI chart? Take note of the filter I have and consider the mandatory sort ascending from 'Serial_No' then 'Entry_Date'.

Hope you got my point. Thank you!

[sunny_talwar · Accepted solution]

May be try this

Sum({<Service_Type = {'EM'}> - <Repair_Code = {'40','41','42','43'}> - <SO_Exemption -= {'Included'}>} Aggr(

If(Serial_No = Below(TOTAL Serial_No) and Below(TOTAL Entry_Date) - Entry_Date <= 7, 1, 0), Service_Order, Serial_No, Entry_Date))

[Anonymous]

Hi Sunny!

Thank you for the concept. Though I have modified the formula as per my needs, your solution is the correct approach!

Below is the modification:

Sum({<Service_Type = {'EM'}> - <Repair_Code = {'40','41','42','43'}> - <SO_Exemption -= {'Included'}>} Aggr(

If(Serial_No = Below(TOTAL Serial_No) and Below(TOTAL Entry_Date) - Entry_Date <= 7, 1, 0), Service_Type, Serial_No, Entry_Date))