Netto_workdays
I'm trying to do a datediff() between two dates in my Cube, but ideally I would remove the weekends from this, like excel would do with the netto.workdays() function.
I do see Pyramid posts on the WORKDAY (PQL - xl) function, but this seems only available in the tabulate module, is there another solution to get the netto_workdays in Formulate or Discover, or do I have to calculate it before entering Pyramid?
6 replies
-
Hi
The example you give is creating arbitrary dates using DateTime() and AddDays(). There isn't a PQL Common DateDiff function that supports excluding weekends, outside of Tabulate.
However, you mention "two dates in my cube". Are you referring to a Date dimension that has contiguous dates, i.e. it contains all the days of the year in a format something like 2025-04-05?
If so, I have a workaround calculation.
LMK
Ian
-
Hey ,
Thank you for getting back to me.
Indeed both dates are continious from "01-01-1970" until "31-12-2030". I would be very interested in your workaround!
Thank you in advance,
Stefan La Rooij
-
Hi
Both dates? Are you wanting to compare dates from different date dimensions?
Also, you mentioned in your post "cube". Is your data in an Analysis Services OLAP cube? Or Tablular? Or SAP BW?
Both these questions have a bearing on the response.
Thanks,
Ian
-
Hey
I had to check with our IT team, they make the models and i'm never too sure about the exact wording.
According to them we just have a SQL server data source. I'm looking to calculate the difference between two date values in different dimensions:
Currently I'm doing this with the following formula:
The one I shared in my initial post was not giving me decimals.
Ideally this would axclude the working days from the sum, but I get the feeling it's going to be easier to pre-calculate this in SQL probably.
Your help is much appreciated!
Kind regards,
Stefan La Rooij
-
create a "custom column" calculation in Formulate - which does the raw difference between the dates in these 2 columns (in SQL). Once you have the difference at the row level, you can start to use the semantic calcs it looks like you're attempting to do. It will be cleaner and possibly more correct this way too.
-
Hi
Here's an example of what is talking about:
Open Formulate and select Custom Column, then choose your model.
Add the DateDiff block and select the first date, then the second date, then the time interval needed.
Create a Measure from the new hierarchy. Aggregate function is average, as it doesn't usually make sense to SUM it. for example for a certain product, probably the average lead time from order date to ship date is more important than the total lead time in days.
Example 1 at the transaction level
Example 2 Average lead time by product
This doesn't give you the net workdays, but it's a start.
hope that helps.
Ian