12 Replies Latest reply on Jul 11, 2016 3:05 AM by Łukasz Majewski

    weekend calculation

    jens.bruckmann

      Hi,

       

      as you already helped me getting my monthly days calculating indipendent from the filter setting I really thought it is an easy task to exclude the weekends. But sadly to say all my starting points went wrong. I really thought I am able to do this with LOD but this did not work either. So any Idea welcome how to calculate the working days without weekend.

       

      Please see attached twbx (9.3 Version)

        • 1. Re: weekend calculation
          Ashish Chaudhari

          Hi Jens,

           

          Create a calculation as below and put this in filter and exclude Saturday and Sunday.

          You will get the desired result.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: weekend calculation
            jens.bruckmann

            Hi,

            thanks for the Idea but this will not help me. I need this calculated without a filter. In addition to this I need to add other fields to this formula (like vacation) and the filter must be based on the country.

             

            So any Ideas how I can add this to a calculated field are very welcome. when you change the country filter you see I have different results in my calculation. This is why I need them fixed

            • 3. Re: weekend calculation
              Ashish Chaudhari

              Hi Jens,

               

              Please refer to the below thread.  I have provided the same solution there.

               

              Last 5 day sales for current month excluding weekends/national holidays

               

              Thanks and Regards,

              Ashish Chaudhari

              • 4. Re: weekend calculation
                jens.bruckmann

                This really is a good approach, but in fact not really what I am looking for. I really need this calculated. When I exclude the days simply by a filter I would not see the sales in your case on the weekend.

                 

                so what I try to do is in my case following:

                 

                ten engineers working 5 days a week. 7days a week on a shift. When I want to find out the AVG per day the engineer worked (excluding weekend, vacation, project...) I first need to find the netto days the guy worked. So my management decided to say: count all days in the month - weekend - vacation -project.

                 

                But the guys are working in different countries. So this is why this must be done via a calculation and not via filter. I thought I can do this with LOD but for any reason the output is related to the filter I set and for some calculations I do not want this.

                • 5. Re: weekend calculation
                  jens.bruckmann

                  I have created a new calculation with LOD but still not working.

                   

                  Any help is really welcome.

                  • 6. Re: weekend calculation
                    Łukasz Majewski

                    Jens,

                     

                    I am happy to help you so would like to ask you to provide a visualization of the desired results (e.g. as excel table) as well as a sample of input data. In your twbx the [Working day] field does not cover all 'business days' - does it mean the records are present only if there was work done on that particular day?

                    Also you may want to have a look at numerous threads related to your case; Here are two examples I participated in:

                     

                    Count the number of workdays in a month

                     

                    DateDiff for Working days only

                     

                     

                    Regards,

                    Łukasz

                    • 7. Re: weekend calculation
                      jens.bruckmann

                      Hi Lukasz,

                       

                      thanks a lot for providing the links, I already worked them through but they were not able to help me

                       

                      The idea I have in my mind I think is really simple but do not work for any reason.

                       

                      I have a table with Dates for each month starting at the first and ending at month end. The first part what I wanted to do is count days in a month indipendent from the filter settings. To do this I received a formula from the community which looks like this (in [Working day] is the raw data):

                       

                      1.: working day year/month

                      date(str(year([Working day]))+","+str(month([Working day]))+",1")

                       

                      2. count the days

                      datediff('day',[Working day YM1],dateadd('month',1,[Working day YM1])

                       

                      this works perfectly.

                       

                      The next Idea I have is to count the Saturday and Sunday each month.

                       

                      But there I already have the issue where I really don´t know how to go forward.

                       

                      I tried already a lot of different formulas but the different filter I need to use are always affecting this.

                      • 8. Re: weekend calculation
                        Łukasz Majewski

                        Right, I still don't quite know what [Working day] records stand for if you only want the number of weekend / business days count in a month - although I have a feeling you will want it counted in your next post

                         

                        Anyways - I used the exact formula from my last link and got the results you're after, I guess:

                         

                        btw: I changed the calculation of your [Working day YM1] to something more simple and effective:

                        1 of 1 people found this helpful
                        • 9. Re: weekend calculation
                          jens.bruckmann

                          hi,

                           

                          well if I do not filter it is working as expected but not when I start to use the filter.

                           

                          workind day is nothing else as a field which has all dates in starting from the first and ending at the last each month

                           

                          Now the big pain is using the filter.

                           

                          As example we only work on a Monday in Ireland and not in Germany so the day will not count as the filter is saying no entry for Germany. But I do not want to have a look what type of filter I use. I really need the calculation on the raw data

                          • 10. Re: weekend calculation
                            Łukasz Majewski

                            The results are the same regardless of filtering any of the countries (unless you filter out all).

                            I do not understand your latter comment.

                            • 11. Re: weekend calculation
                              jens.bruckmann

                              Hi,

                               

                              sorry for my ignorance I just haven´t read the last post of you correctly. Is it possible you can explain me a little bit more about the formula you use?

                               

                              as an example for what is %7? and why to use Parameters? Isn´t it possible to work with Datename="Saturday"?

                               

                               

                              And yes you are my new superhero

                              • 12. Re: weekend calculation
                                Łukasz Majewski

                                Sure:

                                 

                                • % is a modulo operator, that is it yields the remaining number from division's numerator (as integer) - e.g 6%7=6, 7%7=0, 8%7=1
                                • parameter in this example is just to specify what weekdays are non-business days; you may omit this and hardcode to Saturday and Sunday
                                • it is possible to work with datenames like "Saturday" but datepart names are dependent on regional settings and - in my understanding - calculations based on integers are faster and more reliable than those based on strings (text)

                                 

                                Cheers,

                                Łukasz