need a solution

[Anonymous]

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?

[Anonymous]

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?

[jpitamer]

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.