Returning the sum of an expression for the first 10 instances

[RenewalBIUser]

Hello,

I am trying to get this formula to work for the first 10 instances of appointment by our dimension SalesRepName

SUM({<EventType={'Order'}, IsOrderCancelled={0}>} SalePrice)

/ (

COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}>} DISTINCT AppointmentId)

- COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={'Not Home'}>} DISTINCT AppointmentId)

- COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}, IsSameRepResit={1}>} DISTINCT AppointmentId)

- COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}, IsCancelSave={1}>} DISTINCT AppointmentId)

)

I am trying to figure out how to use the Min or First formulas to make this work.  Each appointment has a date (CalendarDate) associated with it that I believe could be leveraged. The goal is to be able to get this calculation by sales rep (SalesRepName) for their first 10 appointments (AppointmentId).  I would like to have this in a table with the formula as a column and SalesRepName as the dimension.  Does anyone know how to get that to work?

Thanks!

Kyle

[Gysbert_Wassenaar]

Can you post a small Qlik Sense app that illustrates the problem?

[RenewalBIUser]

I'm not entirely sure how to illustrate it in the app but this might be helpful.  I simplified the equations a little.

From my data table I want to create the table display.  Does this help at all?

[Gysbert_Wassenaar · Accepted solution]

You can create an sequence number for each appointmentid per sales rep:

Temp:

LOAD CalendarDate, [Sales Rep], $s, AppoinmentId FROM ...source...;

Data:

LOAD

     CalendarDate,

     [Sales Rep],

     $s,

     AppoinmentId,

     Autonumber(AppoinmentId, [Sales Rep]) as Instance

RESIDENT

     Temp

ORDER BY

     [Sales Rep], AppoinmentId

     ;

Then you can use the Instance field in your expressions:

sum({<Instance={'<=5'}>}$s)

sum({<Instance={'<=5'}>}$s)/ count({<Instance={'<=5'}>}distinct Instance)

[RenewalBIUser]

Thank you so much for the response! I think this is going to work but I still have another question to get this to work.  The $s (SalesPrice) are in a different table from CalendarDate, and AppointmentId, and SalesRepName is another table from that.  How do I adjust this script to pull the fields from multiple tables?

Thanks!

Kyle

[RenewalBIUser]

I'd also like to add that AppointmentId is not always generated in order.  To get the first 5, I believe it would need to incorporate CalendarDate or AppointmentDate somehow.

[Gysbert_Wassenaar]

Use only the appointments table to generate a Instance field that sequentially numbers the appointments for each Sales rep. How you can order the records of the appointments table so the appointments of each Sales rep are in chronological order is a question you need to answer. I don't know your data and your business rules.