How do I get a list of all calender dates to use in webi?

[Former Member]

I am using BO 4.1 Sp06,

Sql 2014.

I want to display a stock level on every business date in webi.in a chart

when there is no data for a business date, it should show data from previous date .

I don't have problem with that.

My issue is I don't have all the dates in table it will have date only if there is any transaction happens.

Basically stock level= current stock-(scheduled deposit + scheduled orders)

= A-B

here dates are coming from B, and it will have dates only if there is any scheduled deposit or order on that day else date doesn't exist in the table.

For that reason I need an object to get all the dates and then I can further will be able to filter to get business dates and all.

I don't have any table in DB to get all dates

[amitrathi239]

have you tried with Timedim() function in webi?

[Former Member]

Thank you Amit for your reply.

Can I do something at the universe level instead at the report level? I want to further filter the dates to get business dates, which I am doing in the webi query

[amitrathi239]

you need calendar table in universe to get the continuous dates.if you have calendar table then you can create object in the universe and later can add one more query in webi to get all dates.

You can get the business dates (Mon to Fri) in webi also with function dayname() to exclude Sat and Sunday.

[amitrathi239]

if you don't have calendar table then have you used Timdim() function in webi to get continous date?

[Former Member]

How can I get a calendar table in my universe, when its not there in Db? if you could help on this

[Former Member]

Yes I used it but the thing is for further filters that I applied at the query level those are getting suppressed\ignored with timedim() funcion on report

[amitrathi239]

if you don't have in DB then you can not use in universe.

[Former Member]

See it is something like this on the report, where it should show me continues 5 days from the day I start, I hope I am not confusing you more.

this should show 17th, 18th, 19th, 20th, 21th assuming all are business dates only first 5 business dates, but as you can see there is no data for 20th and 21st for which I need calendar dates

to get this business dates I am filter data at query level to avoid holidays(we have a holiday table)

[amitrathi239]

now use timdim() function on date object and see if you are getting 20th and 21st?

[Former Member]

yes, I am getting all the dates which includes holidays as well.. In my case, we have a holiday table which has all haliday dates. but because use of Timedim() function.. the effect of filter is no more and it retrieves all the dates which includes holidays as well

[amitrathi239]

let say your V date variable look like V date=TimeDim([Date object];DayPeriod)

After that create one more variable V day Name==DayName(TimeDim([Date object];DayPeriod))

Apply filter on V day Name and exclude values Saturday & Sunday.

[Former Member]

in my case its more dates than normal weekend and that holiday data is in the holiday table, I will have to filter data based on that, and then display first 5 records.

[Former Member]

I am trying something like this and it gives me error

=TimeDim([Dates]) Where (TimeDim([Dates])<>([Holiday Date]))

I am using above but getting error #Incompatible