How to get data for current week ,-9,+6 weeks in cross tab report

[84gopi]

Hi,

I need to get current week - previous week ( 9 ) + next 6 weeks of data in cross tab report.
irrespective of the month & year you selece from the report.

For example: if u select july 2012 then it should display current and previous 9 weeks of july & next 6 weeks from August 2012 & sept 2012...........

Please let me knwo how to do this ASAP.

Thanks
Gopi

[pricter]

Assuming that you are using a relational based packaged and sql filter you can add the following filter which retrieves the dates that you want

Code Select Expand

[Date] between
dateadd (
{week},
-9,
dateadd({dw},1-datepart({dw},?Date?),?Date?)
)
and
dateadd (
{week},
6,
dateadd({dw},7-datepart({dw},?Date?),?Date?)
)

I have created the above filter based on "Go Sales(Query)" sample

[84gopi]

hi,

I am using rdbms & retails bi as package. i use MERCHANDISING_WEEK_DIM_ID1 column in order to display week number.

So how to modify below expn using above column......os that I can add one query calculated item in cross tab and do the testing...............But i have 3 cascading prompts in report that will select segment name, family name & session year will display like ........PFA for the same...

So based on the session year week numbers will display in the report and below to this I should get the current week, previous week and next 6 weeks like that...............

In the attachment you find out output format i need to get in my report...........

Thanks
Gopi

[pricter]

It is not clear to me the following

If the user selects Year and not a Date which week of the year is supposed to be chosen as reference?

Do you want to have the current week as reference?

Do you have a date column in your Query?

[84gopi]

Hi,

If you see my attachment we have excel format, there after oct -2012 we have one row which is the i need currently.

For exampleL: if user selects 2012 as session year in the prompt, based on this data will displayed.
1st row it will be number of week id's and in the 2 nd row it will be month& year like July-2012,Aug-2012.......so on.
in 3rd row we shd display no of weeks suppose in the excel we have oct -2012 so below that we have 4 weeks and so on........

So we can take current week as reference.......

Thanks
Gopi

[84gopi]

Hi,

Did you got my earlier reply?

Thanks
Gopi

[Rahul Ganguli]

Hi,

This should work with simple filter condition like the following

weekid between (current_weekid-9) and (current_weekid+6)

You just have to get the current weekid in your query subject.

Regards,
Rahul

[84gopi]

Hi,

Here current week id If I get also I can't get the req ouput....because........we need to get the week no's based on prompt selection of the report where you have season ( month & year combined in cascading prompt)

In one table I have week startdate, enddate, week, week id all these columns.........so based on these can you give me the expression to get req week numbers......

But here one more thing is based on prompt selection, year & month we should get the week id & current, previous 9 weeks & next 6 weeks......

You want me to send xml of current report or you need the snap shot of the output what I am getting currently?

Plz reply me soon..........

Thanks
Gopi

[84gopi]

Hi,

Did you get what I am trying to say in my earleir post?

Thanks
Gopi

[84gopi]

Hi ,

I am sure that my package is relational not dimensional.
Becaue source is not olap & it doesn't contain level & hierarchies as showned in the doc.

My report is cross tab & uses relational modelling now tel me how can I get the required week numbers in my cross tab report based on the season which I select
from the prompt.....

For example: If I select Groceries - as segment name, Groceries - as family name, Season year - Spring2009
then I should get week number starting from 6,7, 8,etc..........in 1st row, then in second row I should get corresponsding month&year.

Now I am already getting above 2 rows problem is with getting week numbers in 3rd row.....by considering 0 as current week and previous 9 weeks(-9)
and next 6 weeks.........PFA of current output what I am getting already & required format I need to get finally i the report which shows week numbers in 3rd row of cross tab.

Please let me know if you need more details.

Thanks
Gopi

[84gopi]

Hi,

One more attachment of current output what I am getting......

Thanks
Gopi

[84gopi]

Hi,

For your earlier expression I have replaced date with business day date, since I have only this date column in my query subject. But I am getting parsing error........

Other than this I have merchandising week, startdate & enddate for merchandising week.......

Please let me know which column I need to use ASAP.

Business Day Date between
dateadd (
{week},
-9,
dateadd({dw},1-datepart({dw},Business Day Date),Business Day Date)
)
and
dateadd (
{week},
6,
dateadd({dw},7-datepart({dw},Business Day Date),Business Day Date)
)

PFA error.

Thanks
Gopi

[MFGF]

Hi,

Your attached document seems to show your expression used in your 'Business Day Date' item. You should be using it in a query filter I think.

You also seem to have entered Business Day Date by hand into the expression - it should be the Business Day Date item from your package, which you can drag in from the queryif you are already using it, and which would be in square brackets [Business Day Date]. You also seem to have confused this with the parameter you are using for your prompt, which should be presented between question marks - ?Date? in the original example.

Regards,

MF.

[84gopi]

Hi,

If use below expression also I am getting many errors

[Business View].[Time].[Business Day Date] between
dateadd (
{week},
-9,
dateadd({dw},1-datepart({dw},?Date?),?Date?)
)
and
dateadd (
{week},
6,
dateadd({dw},7-datepart({dw},?Date?),?Date?)
)


Please tel me what columns I need to change in above expression......I have merchandise week, startdate & enddate in my table.

Plz reply ASAP.

Thanks
Gopi

[MFGF]

What is the name of the parameter you are using in your date prompt? Is it 'Date'?

What errors do you get from your expression currently?

MF.

[84gopi]

Hi,

I am not using any date prompt in my report currently, instead I am using 3 cascading prompts on segmentname,familyname & season year.........

So currently I am researching on how to display curremt week numbers along with previous -9 & next 6 week numbers as I shown my attachment earlier....

So please suggest me how to proceed.....ASAP.

Thanks
Gopi