I’m trying to create a measure that returns 1 if a date is between two dates, or 0 if it is not.
I’m using the following code.
CASE
WHEN EXCALENDARDAY.THEDATE BETWEEN BOABSENCECALENDARLINE.FIRSTDAYOFABSENCE AND BOABSENCECALENDARLINE.LASTDAYOFABSENCE THEN
1
ELSE
0
END
However, when I use this in my report, only the dates that are equal to the first day of absence are given a 1, everything else becomes 0. I’m pretty sure that the reason for this is because I have joined my EXCALENDARDAY to BOABSENCECALENDARLINE (THEDATEDS <-> FIRSTDAYOFABSENCEDS).
What I’m wondering is if there is a way to get any arbitrary day throughout the year and not just the first days. I have tried using a standalone EXCALENDARDAY table (with nothing joined to it), but this gave me the same result.
Correct.
If you wish to use the Calendar for date analysis you would need to use one aliased calendar joined to the start absence date and another alias joined to for the end abesnce date. You could then use the between logic based on your two aliased calendar tables.
While I’m able to create an aliased calendar joined to the end date as well, I still fail to see how this would make it correct. The issue appears to be that EXCALENDARDAY.THEDATE is strictly connected to the BOABSENCECALENDARDAY.FIRSTDAYOFABSENCE, thus making it wrong to use that date to check if it’s between the first and the last date as this would only occur once (on the first day of absence). What I would like to do is to look at every date of the year and see if this date is between the start and the end dates. Is this possible? Or is there perhaps another way to accomplish my desired result?
I’m under the impression that the BETWEEN operator requires me to be able to provide a date that is supposed to be between the start and the end. And as it stands at the moment, I’m unable to find such a date that iterates through the calendar. I can only target the start and end dates.
I have also tried solving this problem in WebI (though I would prefer solving it on universe level), but even then only the first day is found and marked.
I should mention that I’m still new to both UD and WebI.
So, in the terms of data you only have a start of absence and an end of absence in your DB table?
Please explain the business question you are trying to answer. Is it that you wish to return all days of a particular year and flag start and end dates that fall within that year?
To be very specific, my goal is to create an absence calendar with weeks in the horizontal header and employees in the vertical header (ie a crosstab in WebI). I would like to fill each cell with the number of days an employee was absent during that week. My code in the OP should’ve been able to solve this issue, I think, if the date I was looking for wasn’t depending on the first day of absence. So I believe my problem could be solved if I could find a date that relates to my data, yet is not depending on either the first or the last day of absence. I have tried looking into creating a Derived Table, but I’m unsure as to how I would accomplish this.
I tried that as well, but the result remains the same. I’m also using Measure with aggregation: sum. It’s very possible I need to change my code, but I’m unsure of how I should solve this issue.
If it’s not possible, or it seems extremely complex, to accomplish this on universe level, do you maybe know how it could be solved in WebI?
Ah, think I see the problem. Do you have a return to work date?
Not sure this in itself is the problem but it certainly appears to be part of it.
What you need to look at is the concept of aliases. If you’re new to them, dig out the Designer Guide, have a read through then come back to this thread and we’ll carry on. If you’re already comfortable with aliases, just say and we can carry on.
I do have a last day of absence (return to work would be this one +1 day). I inserted an aliased table (EXCALENDARDAY) earlier, as you mentioned I should, and connected it to the last day of absence. Also decided to read a bit more up on aliased tables to ensure I understood what it was, which I now believe I do. So right now I have two different EXCALENDARDAY tables joined to two different dates. But since I already had access to the date for the last day of absence, I’m not able to see how creating the aliased table will solve my issue. Please elaborate.
You have two event date stamps, you can calculate how many days have elapsed between them using the date diff function, if using SQL Server, or subtracting them, if using Oracle?
The problem then is the spread over your weeks. I don’t see the level of granularity, currently, in your data to allow this.
I think this is something that you should take care of in your ETL, giving the count of absence days per week. In addition, I suspect you are only interested in Business days abesence?
I actually already have a variable that’s named DURATION, which gives me the number of days between start and end (and FYI, using SQL). And I’m also interested in all days, not just business days, which I think makes it slightly easier. But is there no way to check an arbitrary date up against the start and ending dates? Create a new timetable?
Yes, it’s a universe based solution so you should be fine.
Without seeing your data it’s hard to suggest a solution - do you have a final day of absence? How are the absences recorded?
First day of absence (date)
Last day of absence (date)
Duration of absence (integer)
I have joined first day of absence to a calendar like this:
FIRSTDAYOFABSENCE (1,1) <-> THEDATE (0,n)
And I have joined the last day of absence to an aliased calendar like this:
LASTDAYOFABSENCE (1,1) <-> THEDATE (0,n)
Although, I’m unsure how I should utilize the aliased calendar as the purpose of adding the initial calendar was only to create a second query in reports so I could make a complete calendar, rather than just a calendar containing the first days of absence. Using SQL code on the first day of absence (date) could otherwise provide me with any day, month, week, year format I wanted.
The calendar itself (at least before the join) contains all dates.
I believe that if I was able to iterate through every day, and not just certain days, then I could check if that date was laying between two other dates. Or if I could maybe populate a new table with all the dates between the start and the end dates and compare this to my original calendar. Please let me know if there is any other information I can provide that might lead us to a solution.
That was exactly what I was alluding to earlier when I suggested the problem is with the granularity of your data. In your case what you require to do this just isn’t there.
Either you would store all the dates between your Absence start date and your absence end date and derive the weekly counts from that, or you would store the counts of dates aggregated by weeks. That is why I suggested resolving this in your ETL.
From the little I have read about ETL, would this mean I have to solve this in my database rather than in my universe? Sadly I don’t think that will be possible for me.
If I could store all the dates between the start and the end dates, that would most likely solve my problem, but I have no idea how to do this. Before asking here, this was one of the things I attempted to google an answer for, without much luck. Could you maybe explain how this would work?
I did look into this link, but implementing that in a derived table was not as straight forward as I had hoped. Maybe not the right way to attack the problem?
Edit:
If the way to go is to use a derived table and then extract all the dates from this one, could one of you maybe look into why this code gives me an error (“Each calculated column must have an explicit name.”). I suppose I could mention that the date ranges should be connected to each employee.
WITH dates AS
(
SELECT CAST(BOABSENCECALENDARLINE.FIRSTDAYOFABSENCE' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM dates
WHERE DateValue + 1 < BOABSENCECALENDARLINE.LASTDAYOFABSENCE
)
SELECT DateValue
FROM dates
OPTION (MAXRECURSION 0)
I suppose I could talk to my superiors regarding solving this on database level, but at the moment I don’t think that is a feasible solution if it can be done, in any way, in the universe.
And when I received that error, I looked it up and got to the same forum as you linked to, but when I try to put in ‘AS’ where you stated I receive another error (“Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000”).
By now I assume you have a good idea of what I want to achieve and what I have to work with, so is it correct to use a derived table? Or do you know of any better way to find all the dates between two dates (throughout the year, possibly several start and end dates, for every employee)?
A derived table is an equivalent to an inline view in SQL.
As I stated before I don’t believe you will be able to solve this easily, derived table or otherwise, in the universe.
I would suggest that you discuss solving this in the database, as I have previously described.
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)
