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

Calendar Fixed Days

Kenneth Holden Dec 11, 2017 11:43 AM

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

Re: Calendar Fixed Days

Kenneth Holden Dec 11, 2017 11:45 AM (in response to Kenneth Holden)

(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
Like Show 0 Likes (0)

Actions
Re: Calendar Fixed Days

Sunny Talwar Dec 11, 2017 11:48 AM (in response to Kenneth Holden)

May be add {1} as set analysis

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

Actions
- Re: Calendar Fixed Days
  
  Andrea Gigliotti Dec 11, 2017 11:52 AM (in response to Sunny Talwar )
  
  you can ignore the subcategory field selection using the below syntax:
  (Count( {< subcategory= >} TOTAL distinct([CREATED_DT.autoCalendar.Date])))*.75
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Calendar Fixed Days
    
    Kenneth Holden Dec 11, 2017 12:23 PM (in response to Andrea Gigliotti )
    
    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,
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: Calendar Fixed Days
    
    Kenneth Holden Dec 11, 2017 12:26 PM (in response to Andrea Gigliotti )
    
    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,
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: Calendar Fixed Days
    
    Kenneth Holden Dec 11, 2017 12:27 PM (in response to Andrea Gigliotti )
    
    its only considering a day to exist if a sale(created date) existed on that date,
    
    Like Show 0 Likes (0)
    
    Actions
- Re: Calendar Fixed Days
  
  Kenneth Holden Dec 11, 2017 12:11 PM (in response to Sunny Talwar )
  
  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
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Calendar Fixed Days
    
    Sunny Talwar Dec 11, 2017 12:13 PM (in response to Kenneth Holden)
    
    Have you tried what Andrea Gigliotti mentioned and see if that works
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Calendar Fixed Days
      
      Kenneth Holden Dec 11, 2017 12:14 PM (in response to Sunny Talwar )
      
      not yet as i want to be able to filter by a number of options, and this would only apply to one.
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Calendar Fixed Days
        
        Sunny Talwar Dec 11, 2017 12:25 PM (in response to Kenneth Holden)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Calendar Fixed Days
        
        Kenneth Holden Dec 11, 2017 12:41 PM (in response to Sunny Talwar )
        
        That works as well, Thanks
        
        I think I've isolated the issue, teh source file(excel) does not have rows with 0 values for days where no sales happened.
        
        When I choose my whole business obviously have sales every day(created date) so it gives the right total number of days because there are no 0 values.
        
        when I filter by the supervisor the above solutions work provided but the number still changes, reason being, a small team of reps have days where they had no sales so Qlik is only counting day with sales(created date). There are no rows with 0 values for days with no sales.
        
        this is the issue
        
        so a new question: is there a way to add a calendar to qliksense so I can simply count days between a period? ignoring my source file which is only creating calendar dates for days there were sales.
        
        Thanks
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Calendar Fixed Days
        
        Luis Madriz Dec 11, 2017 9:02 PM (in response to Kenneth Holden)
        
        Hi Ken,
        
        I created this subprocess to create master calendars. I hope it helps
        My 2 cents on Time Analysis
        
        Cheers,
        
        Luis
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post