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
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.
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:
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.
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:
workingday.twbx 19.0 KB
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
The results are the same regardless of filtering any of the countries (unless you filter out all).
I do not understand your latter comment.
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
- % 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)