12 Replies Latest reply: Dec 11, 2017 9:02 PM by Luis Madriz RSS

    Calendar Fixed Days

    Kenneth Holden

      (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!