Filter on Last Day of Previous Month (Criteria)
Can someone please advise me why I can't use this as a SQL Filter?
| "Gregorian Calendar"."Date" = TIMESTAMPADD(SQL_TSI_DAY , -(1), TIMESTAMPADD(SQL_TSI_DAY , DAYOFMONTH("Gregorian Calendar"."Date") * -(1) + 1, "Gregorian Calendar"."Date")) |
When I put the expression in a formula it returns the correct date 12/31/2018 .... but it doesn't work as the criteria for a filter...
Similarly I would also like to filter on the last day of the previous year. Can someone please assist me? Thank you.
Comments
-
mrmmickle1 wrote:but it doesn't work as the criteria for a filter...
Can you define "doesn't work" ?
Do you have an error? Do you just don't have any row back?
Before to go into how to find the solution, let's look at your formula, because you are doing your best to overkill it:
You take your date, you subtract the DAYOFMONTH and add 1, then you subtract another day.
Isn't is the same exact thing if you just write TIMESTAMPADD(SQL_TSI_DAY , DAYOFMONTH("Gregorian Calendar"."Date") * -(1), "Gregorian Calendar"."Date") ?
Time calculation aren't free of charge, they cost quite a lot in general ...
Assuming you don't have errors (or it wouldn't be nice to not post it ), did you try taking the generated physical SQL and debug that one? You maybe have the time jumping into the = and breaking it. When you play with dates I believe OBIEE strip the time component, while your column maybe still have it and it's also maybe not set at 00:00:00 because of some timezone or things like that.
Therefore taking the physical SQL and fixing that one will point you in the right direction for fixing your filter.
For the last day of previous year there is a function returning the DAY number in the year (if not wrong), taking that one for your calculation.
PS: Can you please re-flag this as a question? Because you have questions, and you explicitly unflagged it, which will not help other users in the forum, not how a community forum works. Thanks
0 -
"Gregorian Calendar"."Date" = TIMESTAMPADD(SQL_TSI_DAY , -(1), TIMESTAMPADD(SQL_TSI_DAY , DAYOFMONTH("Gregorian Calendar"."Date") * -(1) + 1, "Gregorian Calendar"."Date"))
You need to replace "Gregorian Calendar"."Date" with CURRENT_DATE on the right side of the equation.
0 -
Hey Gianni-
Thanks so much for taking the time to review my post. By it doesn't work I mean I get No Results. If I filter on the hard coded date 12/31/2018 I do get results. If I enter the expression in a formula I get the expected result. I don't have access to run to do any sort of debugging in the environment. However, I have seen some time statements that look like this.... so maybe the issue is the time as you have referenced. I just found the referenced time calculation onlin but, I see where you're going with the complex calculation. It can indeed be simplified. :
I bleive this is 2 years ago so 12/31/2017
cast("Gregorian Calendar"."Year" as integer) = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -3 ) else (cast(valueof("CURRENT_YEAR") as integer) -2 ) end
I would gladly flag this post as a question but I don't see the option when I click Edit. Maybe I'm missing something?
0 -
Hey Jerry-
That comment got me to the answer. I appreciate it:
"Gregorian Calendar"."Date" = TIMESTAMPADD(SQL_TSI_DAY , -(1), TIMESTAMPADD(SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
or more efficiently like Gianni
TIMESTAMPADD(SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -(1), CURRENT_DATE)
0
