Yesterday's data in webi report

[Former Member]

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.

Thanks,

Satz

[deepak7]

Use the function RelativeDate().

For e.g., Yesterday = RelativeDate(CurrentDate();-1)

[former_member309630]

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)

Thanks,

Durga

[Former Member]

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.

Regards

Venkatesh