Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kennethholden
Contributor

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?

11 Replies
kennethholden
Contributor

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

Digvijay_Singh
Honored Contributor III

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?

kennethholden
Contributor

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

kennethholden
Contributor

Re: need a solution

only not under

kennethholden
Contributor

Re: need a solution

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

kennethholden
Contributor

Re: need a solution

field

Digvijay_Singh
Honored Contributor III

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.

jpitamer
New Contributor II

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.

kennethholden
Contributor

Re: need a solution

amazing.

thank you so much.

Community Browser