
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calendar Fixed Days
(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!
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
(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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be add {1} as set analysis
(Count({1} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you can ignore the subcategory field selection using the below syntax:
(Count( {< subcategory= >} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you tried what agigliotti mentioned and see if that works

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
not yet as i want to be able to filter by a number of options, and this would only apply to one.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- « Previous Replies
-
- 1
- 2
- Next Replies »