I have setup a calendar to use for Working Days. I use it for average daily sales. I want to have the ability to use the WD count for the month in a calculation. It seems it only knows how many days have happened. I want this in custom mode to do a projection based on ADS X WD for month. Any thoughts would be appreciated.
Thanks,
Hi Jim,
You can use the {Period_Days} placeholder within custom mode to return the value of days within a specified period, outlined in the help documentation here .
Keep in mind, this will simply return the total number of days, whether they are defined as working or non-working days. This issue has been raised on the forum before here .
Hope this helps!
I went down the road of building a stream for working days but found it doesn’t work as the mapping of dimensions gets broken.
We ended up having budgets broken down by day to give us what we needed.
The solution to forcing Phocas to understand Working Days ultimately will come from Phocas developing a Function you can call in a report that links to the actual calendar you use rather than just the days in a period. Unfortunately, what I’m told is that Phocas aren’t pursuing this at the moment - which is a shame - though more posts like this will help escalate it as well as pushing your Account Manager too.
Thanks for the feedback. Frustrating when you know what you want, the data is there, and feel it should be able to do it.
Jim Mueller | Vice President of Operations
www.engman-taylor.com
C: 815-978-6967 | D:
815-668-4356
O: 815-282-1211 |
F: 815-282-0606
I think you would be able to this by loading up Working Days as a measure. If your report is by Customer, you would need to load up a list of Customers, with a 1 for every working day.
It is a work-around, but I think you could make it function the way you want with the right data model.
Exactly, the measure in a stream doesn’t work as there is no mapping to dimensions or properties.
What we discovered is that you need that working day record per dimension and per property so the table ends up huge (although it can be created dynamically and shouldn’t change once built).
This was new to Phocas and not how they normally do things, I believe it’s something better pursued as a core Phocas feature as it’s more complex than just adding streams.
Yes, I agree this is just a workaround and not ideal.
It would be great if they could make it a core feature.
Hey,
i needed exactly the same calculation and the guys at support in the UK set it up for me.
^(SELECT cast(Sum(Value) as numeric(5,0)) FROM [PhocasMaster].[dbo].[WorkingDay] where CalendarID = 4 and year(dateadd(dd,-1,Getdate())) = year(day) and month(dateadd(dd,-1,Getdate())) = month(day))
Frank
I did push it to Phocas tech support and it sound like they are going to do something for me on this. Not exactly sure why yet, but i will be sure to post what they setup for me.
Hi @StuartH, it was nice talking to you yesterday. I will set up the same calculation as I did for @frank.kearns when I start working on your requirements.
Thanks @nyphocas
I’ve been lobbying Phocas to introduce Functions similar to the {PERIOD_DAYS} and {PERIOD_COUNT} as follows:
If we can get Phocas to use this logic and convert it into an easily called function it would be great.
In this instance you could call it {CM_WD}.
Expanding on that you could have {MTD_WD} for Month to Date, {ROM_WD} for Rest of Month – the same could be applied to Weeks and Years (WTD / ROW, YTD / ROY), .
Ideally I’m trying to get to being able to calculate a run rate of the past X days and multiply it by the remaining Y days this period (week, month, year) – excuse that my brackets may be wrong, my SQL isn’t what it used to be:
{YTD_WD}
^(SELECT CAST(SUM(Value) as NUMERIC(5,0)) FROM [HARRISONCLOUGH_Master].[dbo].[WorkingDay] WHERE CalendarID = 1 AND BETWEEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear AND DATEADD(dd,-1,Getdate()) AS Yesterday)
{ROY_WD}
^(SELECT CAST(SUM(Value) as NUMERIC(5,0)) FROM [HARRISONCLOUGH_Master].[dbo].[WorkingDay] WHERE CalendarID = 1 AND BETWEEN DATEADD(dd,0,Getdate()) AS Today AND DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear)
@StuartH,
Did Phocas work this out for you? I have a need to show projected sales based on working days too. I’ve been all over here trying to find something definitive, but really haven’t thus far. It’s been a few months since the last posting and I was just curious if anyone had this worked out yet. Cheers!
