Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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?
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
only not under
i guess I could create a filed that actually counts the number of reps each week available
field
May be, as you need something to differentiate that you are not counting total reps but the total reps from week1.
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.
amazing.
thank you so much.