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
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?
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.