Previous three months data

building semantic layer
1

Hello,

I need help to create a logic within the universe layer to show only the previous three months data.

My universe has been built based on SQL server database. The snapshot data is going to be loaded every night to the database. I created a WebI report and at this moment the Graph in the webi report is showing the whole 6 months data.

I have to show only the last 3 months data (whole period) based on the snapshot date.

Because of the huge data valume, I prefer to create something like a predified filter of prompt in the universe.

Could anyone help me?

Many thanks,
Gazelle

Gazelle110 (BOB member since 2012-08-05)

2

Did you search in the forum? You can create pre-defined filters to get last 3 months data based on system date (getdate()). But I think you want to do this based on the snapshot date…

Can you create a filter like something like - snapshotdate >= select date_add(m,-3,max(snapshotdate) from )?

cpmohanraj :australia: (BOB member since 2002-09-23)

3

Hello,

Thanks a lot. The filter, it works :smiley: .Now I need to do the same for the prompt. I need to create a prompt to pick up only the latest 3 months.

Do you may have any idea also for this?

Many thanks,
Gazelle

Gazelle110 (BOB member since 2012-08-05)

4

Can you explain more about the prompt requirement?

cpmohanraj :australia: (BOB member since 2002-09-23)

5

Sure, I need to create a prompt based on the snap shot date. For instace I have 6 months snapshot (weekly data) like this:

9 May (Monday of the week)
16 May
23 May
30 may
…
…
…
…
2 July
9 July
16 July
23 July

I need to create a promt to let the use select only the latest three months when she click on “refresh button”. She needs to see only three months snapshot data back in her selection and not all the 6 months.

I tried something like this but it doesn’t work:

@Select(Service report Request Management\Today date) BETWEEN (dateadd(month, -3, getdate()) AND getdate()) = @Prompt(‘Please select the report creation date’,‘D’,mono,free))

Cheers,

Gazelle110 (BOB member since 2012-08-05)

6

Create your condition like below:

@Select(Service report Request Management\Today date) BETWEEN (dateadd(month, -3,@Prompt('Please select the report creation date','D','Class\SnapshotDate',mono,free) ) AND @Prompt('Please select the report creation date','D','Class\SnapshotDate',mono,free))

this condition will bring previous 3 months data only based on the user selection date.

BO_Chief :us: (BOB member since 2004-06-06)

7

That’s perfect!

cpmohanraj :australia: (BOB member since 2002-09-23)

8

Hello,

I’ve a very similar problem, I’ve tried with your code buy my database is Oracle and I don’t know how to subtract three days.

Could you help me with that?

Thanks

Toxicosmos (BOB member since 2012-08-21)

9

In Oracle for 3 days use:

SYSDATE - 3

BO_Chief :us: (BOB member since 2004-06-06)