displaying last 14week date's in the value prompt .

[lalitha.nov20]

Hi,

I have a requirement where I have to show last 14 week starting from the currentweek in the value prompt.

When the report is executed, it should auto execute for current week and should have a prompt ion the report page showing last 14 week date's in the drop down value prompt.

i am using a DMR model and date's in the time dimension are in varchar format.

Please let me know how to go about it.

Thank you,
Lalitha

[wyconian]

HI

Do you have weeks in your time dimension?  If you do you can use a function like lag(time_dimension, 14) to give you the previous 14 weeks.

Good luck :-)

[charon]

iF you have a relational modell, you might create a query just for the prompt and use a detail filter with adddate" sysdate" function. The query should contain weeks only...

cheerz & gl

[lalitha.nov20]

Hi,

"Do you have weeks in your time dimension?  If you do you can use a function like lag(time_dimension, 14) to give you the previous 14 weeks.

Good luck :-)"

I can use lag but my time dimension has date till 2016 and our i require to point to current system week and then display the last 14 weekd.

Thank you,
Lalitha

[wyconian]

OK so you need to identify the current week/day.  Do you have a flag in your date dimension that does that?  If not you can use the today() or now() function to identify the system run date when the report is run.

You can then use this date as a filter in your query to restrict the data

[FabianGaussling]

Hi,

in an oracle environment I would try to filter your prompt-query with something like:
 
     [Date Dimension]...[Date] between sysdate-7*14 and sysdate

Fabian

[lalitha.nov20]

dimension date are in varchar format mm/dd/yyyy and system date is in date format. Can you provide met he filter expression how do I apply the filter on the date dimension.

Thank you,
lalitha

[Satheesh]

Hi,
Could u tell which type of model ur using, Relational or DMR...???
and what is Ur DB...???

[lalitha.nov20]

I am using a DMR model

[Satheesh]

in Query try to use Filter like below



[Sales (analysis)].[Time].[Time].[Day].[Week of the year] between _week_of_year (current_date)-14 and _week_of_year (current_date)
and
[Sales (analysis)].[Time].[Time].[Year].[Year] = extract (year,current_date)


by using above query you can filter the last 14 Weeks from the current Week.


and Add Day Level as a data item if u have.




look the attachment...