Urgent Help - 4 Weeks data using Query filter

[former_member305206]

Hi Experts,

We had a requirement of creating a chart which should show current week + previous  3 weeks of data with an input control of Week number  that is if we enter week 19 in the selection field in input box we can fetch 19,18 ,17 ,16 data . We have now acheived it

Please refer the screenshot of the report which we have created .

However now the requirement changed . Client wanted us to use the week number at query level with the same functionality .

Like -if we put week number filter in the query level and when the user selects the week number while refreshing the report then it should fetch current + previous 4 weeks . Please help me as how to create the same functionality in query filter . It is very urgent.

Looking for your reply

TX,

DP

[Former Member]

try below solution:

create two objects on universe

Star_Week =if [week]-4 <= 0 then (52-(4-[week])) else [week]-4

end_week = [week]

Query1:

On this query provide prompt for user on [week] object & in select choose start_week & end_week objects

Query2:
On this query apply where condition on week object like

[week] >= end_Week and [week]<= start_week

where end_week would be result from another query & same for start_week.

So by using this logic you can achieve your output.

Thanks,

swapnil

[Former Member]

What is your data source? Bex, Oracle, SQL Server, etc?

[former_member305206]

Teradata

[Former Member]

OK, my Teradata isn't great but the basic idea is to build a predefined condition in your universe.

As long as you can work out the current week ending date then you can build the condition. Pseudo code will be something like:

table.transaction_date between current_week_ending_date - 27 and current_week_ending_date

I *think* it would be something like:

table.transaction_date between ((DATE-DAYOFWEEK(DATE))-20) and ((DATE-DAYOFWEEK(DATE))+7)

Regards,

Mark

[former_member305206]

Thanks Mark for the quick reply . Let me try this

TX,

DP

[former_member305206]

Mark,

Could you please elaborate ..Iam not able to understand it clearly

Note: I already have a Database column as Week number .

I am not supposed to do anything in the database level .I have access to perform at universe level and webi level

TX,

DP

[Former Member]

So you don't have a week ending date?

[former_member305206]

we have calendar date

[Former Member]

So, can you tell from calendar date what fiscal week you're in?

[former_member305206]

2015

[Former Member]

No I meant does your calendar have calendar date and fiscal week side by side so you can look at a date and see what fiscal week it is?

[former_member305206]

[former_member305206]

Mark this is the data and columns related to the date in the date table

[Former Member]

Why have you got so many rows for the same day?

[former_member305206]

That is how it is in the database

[Former Member]

Are there time values associated with the rows?

[former_member305206]

Yes they are associated