Search code examples
business-intelligencebusiness-objectswebi

Datedim function not returning yesterdays date webi

My Datedim function is not returning yesterdays date in webi, any ideas on how to show 13/04/2022, even if it has null values?

Thanks

enter image description here

Solution

  • If you have gaps in your date data the simplest way to fill them in is to create a variable with the TimeDim() function. However, that will not work for you since you do not have a true gap because your missing date is at the end.

    You need a data source with all the dates you want to display regardless of if you have data for those dates or not and then merge on your date dimension. I answered a question very similar to this here. I am copying my answer from there below...

    The TimeDim() function will fill in the empty periods in your time data. The problem with that though is if it is the end of your date range that is missing data those dates will not show up. Let me show you what I mean. Here is my sample data from 12/01/2021 through 12/26/2021 (note missing dates) in the table on the left. The table on the right is the my Var Data Date TimeDim variable defined as…

    =TimeDim([Data Date]; DayPeriod)

    Missing Data

    So we have our missing dates in the middle, but not at the end (12/25/2021 and 12/26/2021). To get those dates you need a query to return all the dates in your specified range. If you have a universe based on a calendar you could use that. Free-hand SQL based on a calendar table would suffice as well.

    If you have neither of those we can still get it to work using free-hand SQL with a CTE. This is SQL Server syntax. You will have to modify this SQL to work for whatever database platform you have if it isn’t SQL Server.

    Here is the SQL…

    ;with dates ([Date]) as (
Select convert(date,‘2021-12-01’) as [Date] – Put the start date here

union all

Select dateadd(day, 1, [Date])
from dates
where [Date] < ‘2021-12-26’ – Put the end date here
)

select [Date]
from dates
option (maxrecursion 32767) – Don’t forget to use the maxrecursion option!

    Source: Generate a Date Table via Common Table Expression (CTE) | Data and Analytics with Dustin Ryan

    Here is a demo.

    Now that you have a query returning all of the dates in your range you can merge the date from that query to your Data Date.

    You can then put the date object with all of dates or the Merged Date in table with any measures from your pre-existing query and there you have it.

    Missing Data 2

    If you need to add dimensions from you pre-existing query I think you will need to create variables for them with Qualification set to “Detail” and the Associated dimension set to “Merged Date” (or whatever you called it). And if you do that I believe you will also need to check “Avoid duplicate row aggregation” check box within the Format Table properties.

    Missing Data 3

    Let us know how it goes.

    Hopefully that will get you on the right track.