Skip to Content
avatar image
Former Member

Yesterday's data in webi report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    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.

    Regards

    Venkatesh

    Add comment
    10|10000 characters needed characters exceeded

  • 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)

    Thanks,

    Durga

    Add comment
    10|10000 characters needed characters exceeded