Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RenewalBIUser
Contributor III
Contributor III

Returning the sum of an expression for the first 10 instances

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
RenewalBIUser
Contributor III
Contributor III
Author

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

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)


talk is cheap, supply exceeds demand
RenewalBIUser
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.


talk is cheap, supply exceeds demand