Calendar Fixed Days

[Anonymous]

(Count(TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

the above expression works on all aggregates as long as not filtered by a specific subset category. It is designed to calculate the dates that sales occurred on.

in my source file, there is no 'null' values or rows for weeks with no business.

What I am trying to do is have Qlik calculate the total number of available days during the period from the beginning of fiscal year(0ctober 27 2017), so I can measure a sales reps productivity  by how many days they submitted sales in the available total days, so 'days with sales, divided by total available days to sell in"

the .75 is to allow for two days per week off such as a weekend etc. so 5 eligible days so to speak per week.

The issue is when I filter by a subcategory say "supervisor is stock" the total number of days changes, it should be constant.

any help appreciated to tell qlik to not change total number of available days? Follow up question is, can I tell qlik which date is the start of the fiscal year?

thanks so much qlik community!

[Anonymous]

(Count(distinct([CREATED_DT.autoCalendar.Date]))/((Count(TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75))

this is another expression designed to discover their productivity based on above

[sunny_talwar]

May be add {1} as set analysis

(Count({1} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

[agigliotti]

you can ignore the subcategory field selection using the below syntax:

(Count( {< subcategory= >} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

[Anonymous]

Thanks Sunny.

This is indeed keeping the count of days the same, however, it is now counting all available days by number of reps, so rather than giving me the answer 35, it is saying 830

see total work days is value of 830, it should be 35, there is actually 47 days since oct 27, I am getting to 35 by multiplying by .75 to reflect the fact 99% of people take two days off.

see pic below to give fuller picture

thanks for your assistance

[sunny_talwar]

Have you tried what agigliotti mentioned and see if that works

[Anonymous]

not yet as i want to be able to filter by a number of options, and this would only apply to one.

[Anonymous]

Thanks Andrea

this worked, but when I filter the number of fixed days changes.

I think the issue is that when I filter by a supervisor QLIK now only counts their total available days,

[sunny_talwar]

Then may be allow for the few you would want to impact

(Count({1<Field1 = p(Field1)>} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

or this

(Count({1<Field1 = $::Field1>} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

[Anonymous]

I meant to add, Qlik is not counting available days, its counting created date.calendar, or basically only total days that sales were made on vs the number of actual days available since oct 27

so when I filter by supervisor, their total number of days with sales is less than my whole business obviously.

I need to tell qlik to count all days from oct 27 to whatever present date is, vs created date,