Skip to Content

Yesterday's data in webi report

Nov 15, 2016 at 03:18 PM


avatar image

Dear Experts,

I have created a Dashboard to report on YTD, MTD, QTD and Today's data. When user selects today's date in promot, the above data should display in the dashboard. Dashboard is working fine.

User had a new requirement to display data for yesterday also in the above report when he select today's date. I created variables and wrote the formula below and it works.

=[Total Hours] Where ([Hours Year] = [UserInputYear] And [Hours Month] = [UserInputMonth] And [Hours Today] = [UserInputDay]-1)

But the Issue is on 1st day of the every month. Let's say the user selects 1st of November and he should get yesterday data of 31st october.

As per the above formula [Hours Today] = 1 and [UserInputDay]-1 gets 0. So the day nos doesn't match and even the month nos also doesn't match. so the report doesn't display any data.

Please can some one help me in this. I have checked many blogs and used the Relativedate function too and the formula doesn't work.



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Venkatesh Katta Nov 16, 2016 at 04:57 PM

Hi Satz,

I have a similar situation to subtract 1 month from current month.I have given similarly like '-1' it worked fine up to December. In January it failed

solution was instead of '-1' I gave '-1 Month'.My Database was 'DB2'.

we have done similar things for rolling back to 10 days as 'currendate() - 10 days' currentdate()-10 will work if date above 11.

This is as per 'DB2',

Please check for similar options in your respective DB.



10 |10000 characters needed characters left characters exceeded
Sai Prasad Nov 28, 2016 at 10:15 PM

Hi Satz,

Please try like below.

1.Create a another data provider with date object and same object use in prompt and give prompt syntax as "Enter date:"

2.Create a Filter at universe level like.

Syntax: Table.Field= @Prompt('Enter date:','A','Table.Field field path',Mono,Free,Persistent,{''})

Table.Field = which we have given in prompt

Table.Field field path : which we have given in prompt field path in universe.

3.Add this universe Filter in report first data provider(which you using to show the data in the report)



10 |10000 characters needed characters left characters exceeded