Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kluckow0128
New Contributor II

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

Re: Returning the sum of an expression for the first 10 instances

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
6 Replies

Re: Returning the sum of an expression for the first 10 instances

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


talk is cheap, supply exceeds demand
kluckow0128
New Contributor II

Re: Returning the sum of an expression for the first 10 instances

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?

Re: Returning the sum of an expression for the first 10 instances

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
kluckow0128
New Contributor II

Re: Returning the sum of an expression for the first 10 instances

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

kluckow0128
New Contributor II

Re: Returning the sum of an expression for the first 10 instances

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.

Re: Returning the sum of an expression for the first 10 instances

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