Analysis :: How To Get Cumulative Count Of Business Days
Jul 9, 2015
I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.
WITH MEMBER [Measures].[Working Days] AS COUNT(Date.[Working Date].&[1])--Doesn't work MEMBER [Measures].[Average Daily Dispatches] AS [Measures].[Total Dispatches]/[Measures].[Working Days] SELECT [Measures].[Average Daily Dispatches] ON 0 FROM [cube] WHERE ( [Date].[Month].&[2015-01-01T00:00:00]:[Date].[Month].&[2015-08-01T00:00:00] );
I have a cumulative measure in Tabular, and when I filter by a date in SQL it brings back all rows up to that date. However, when I drill down on any of the numbers, it only brings back rows related to the filtered date, and not the cumulative rows. Is there any way round this at all? Major obstacle for me at the moment...
I'm looking for a DATEDIFF function which will give me the BUSINESS days difference between 2 dates. e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4. is there such a function? Thx
I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:
Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".
So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:
I am trying to create a calculated member for Cumulative sum using Fiscal Calendar using the below code:
WITH MEMBER [Measures].[No of Accounts Cumulative] AS 'Sum(PeriodsToDate([DimFinancialDate].[FiscalMonth].[(all)],[DimFinancialDate].[FiscalMonth].CURRENTMEMBER), [Measures].[No of Accounts])' SELECT {[Measures].[No of Accounts],[Measures].[No of Accounts Cumulative]} ON COLUMNS, [DimFinancialDate].[FiscalMonth].Members ON ROWS FROM [Acquisition] where [DimFinancialDate].[Year - Quarter - Month - Date].[FiscalYear].&[2014-04-01T00:00:00];
Getting below OUTPUT:
No of Accounts No of Accounts Cumulative All 1763 1763 Apr 14 116 353 May 14 30 383 Jun 14 284 667 Jul 14 112 779 Aug 14 38 817
[code]....
Basically, It is taking all the members of the Fiscal date dimension to compute the output, But what i am expecting is to compute only for Fiscal calendar that is from April 2014 - March 2015.
Hi and Thank you in advance I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates. Example if Date1 = 11/26/1999 Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2 I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates. Thank you in advance Tomas
Hi and Thank you in advance I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates. Example if Date1 = 11/26/1999 Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2 I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates. Thank you in advance Tomas
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc) - y axis; the cumulative number of orders
The chart needs two series:
Previous Week. The running count of orders placed that week. Current Week. The running count of orders placed this week.
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
- [URL] ... - [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
I have a calendar table against entire year 2015 with each day with 2 flag,
1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N" 2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------ Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19 Select statements included within a function cannot return data to a client. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20 Invalid column name 'WorkDay'. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22 Invalid column name 'Date'.
------ my function code ---------------------------- CREATE FUNCTION [dbo].[FGetWorkDate]( @WorkDays VARCHAR(5), @DateFromValue AS DateTime ) RETURNS DATETIME
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
The logic of MDX eludes me oftentimes... Why the subcube query below will not return the expected result. I'm using the SSAS browser to test a calculated member, and found the query it is generating uses several layers of sub-queries. I've managed to replicate the issue in a simpler query from 2008 Adventure works:
with member x as count(existing ([Date].[Date].[Date])) select non empty { x, [Measures].[Order Count] } on 0 from (select ([Date].[Date].&[20080601] : [Date].[Date].&[20080602]) on columns from [Adventure Works] )
Order Count returns the right number, 287 for the 2 days that are in the subcube. x however, returns 2191 instead of 2.
I've tried a lot of variations using filter and descendants, have also tried different levels of the dimension such as [Date].[Date]. I get some different numbers, but never 2.
If I include the Date hierarchy on axis 1 (rows), I get the right answer of 1 day for each row, and there are only 2 rows. But if I use any other dimension on rows, it repeats the value 287 for order count, and whatever strange number there is for x.
So here is the question, on the last line, MAX(LAST T.LastDate) has a date, or it's NULL.
How can I change the statement to say, IF MAX(LAST T.LastDate) IS NULL use NULL?
SELECT DISTINCT TOP (100) PERCENT O.CompID, O.Comp_Name, COUNT(DISTINCT O.Comm_Unit) AS Apartments, FL.First_MI, FL.Last_MO, O.Bldg, DATEDIFF(day, FL.First_MI, FL.Last_MO) AS days, O2.MoveoutDescription FROM dbo.OccupancyHistory AS O RIGHT OUTER JOIN (SELECT O.CompID, MIN(FIRST_T.FirstDate) AS First_MI, MAX(LAST_T.LastDate) AS Last_MO, MAX(LAST_T.LAST_ID) AS LAST_ID
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT non empty [Measures].[TRANSACTIONS Count] on 0, non empty ([TRANSACTIONS].[Days].[Days], [TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER [Measures].[Cumm Account Count] AS ( AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count]) )
SELECT non empty [Measures].[Cumm Account Count] on 0, non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
I am trying to compute average of Sales amount for 10 days and I am having an issue when using LAG function. Since weekends fall in between working days my LAG (10) function is also including weekends and hence I am not able to get the correct average.
in cube we have one role defined. users in that cube should be allowed to see only last 2 days of data. I mean they should be able to browse only last 2 days of data based on current date. How to implement?
