11 Replies Latest reply: Mar 27, 2018 12:35 PM by Jesus Pita Merino

# need a solution

i am using the following expression

=count(distinct(agenctlastname)/count(total distinct(agent last name)

This gives me the % productivity of active reps.

I am attempting to use this expression to figure out what the rep productivity was in a specific week. The problem it is uses the number total reps as of the current moment vs the number of sales reps previously.

For example:

week 1

week 2

week 3

in week 1 say I had 25 reps

in week 2 i had 27 reps

in week 3 I had 30 reps,

the expression is using 30 reps for all weeks. How do i write the expression so qliksense will only total the number of reps that were available in week 1 for example?

• ###### Re: need a solution

what I am trying to say is in week one maybe i had 25 reps total, but only 20 wrote business,

in week 2 i now added to more reps so I have 27 available, and maybe 20 wrote business.

so as each week goes i may add more reps or maybe some resigned and left

each week may have a different number of available reps

what my expression is doing is assuming that all weeks have the current available reps, so if I have 40 reps by week 20 it then says i had 40 reps also in week one, this is therefore now giving me the wrong percentage as less reps were available to sell in week 1

• ###### Re: need a solution

I think you would need some kind of joining date or any other filter field to understand that in Week 1 you had only 20 reps active. Can you share if you have such fields in your data model?

• ###### Re: need a solution

Thanks.

I do not have any joining dates, I was hoping maybe there was a way to tell qlik to to under divide by a count cumulative number based on a progression, week 1, 2, 3 etc

• ###### Re: need a solution

only not under

• ###### Re: need a solution

i guess I could create a filed that actually counts the number of reps each week available

• ###### Re: need a solution

field

• ###### Re: need a solution

May be, as  you need something to differentiate that you are not counting total reps but the total reps from week1.

• ###### Re: need a solution

Hi Kenneth,

Use the TOTAL <field> function. In your case this should be:

/count( total <FIELD WHO CONTAINS THE WEEK>  distinct(agent last name)

The use of <> delimiters is mandatory. This should count the total agents for the selected week instead of the whole amount of agents.

Regards.

• ###### Re: need a solution

amazing.

thank you so much.

• ###### Re: need a solution

Can I ask one more question?

my source file only has records for when there was activity, for example, if a sales rep had no activity in say week 10  there is simply no record for that rep in that week.

This throws off my averages obviously, is there a way in a KPI object to tell qlik for a distinct week if a sales rep has no activity to always pt 0 so the averages work out?

• ###### Re: need a solution

Hi Kenneth,

In this case there is no function to do this. You must "create" additional data for all the reps for each week into the load script but with null or 0 sales value, maybe using a left join or something similar. This will allow you to count all the reps in week 10 (and into all the weeks with no sales data) but all the sales reps will have 0 or null sales value instead of having no sales data for week 10.

Warning! Be aware that this will increase your data size and maybe have some impact on your document performance.

Please mark the previous answer as correct if it was useful to you.

Thanks and regards.