Hello Experts,
I wish you & your family Happy New year 2012.
Currnetly am facing issue while am calcluating varience for week.
In my cross tab report , i want to find a week varience.
For ex: If user selects Dec 22, 2011 then data has to display from dec 12 to dec 22 2011.
simialry if nxt day user selects dec 23 then also he has too see the data from dec 12 to dec23.
Could you pl tell me how to find varience and how to display it in d crosstab report.
Thanks in adv!!!
From the dates given in your example I deduce you need data related to the week associated with the date entered + the week before?
I would use both the _day_of_week and _add_days functions in a case statement to determine the cut-off for the first date of the range.
However this also depends on how you define a week. Do you mean it to start on a monday?
im not 100% sure about ur needs.. but with ur sample condition as 22,23 dec which should bring report from previous monday upto today of current week, U can go with this
create a data item in ur query as
CASE _day_of_week( current_date , 1 )
WHEN 1 THEN _add_days ( current_date, - 8 )
WHEN 2 THEN _add_days ( current_date, - 9 )
WHEN 3 THEN _add_days ( current_date, - 10 )
WHEN 4 THEN _add_days ( current_date, - 11)
WHEN 5 THEN _add_days ( current_date, - 12)
WHEN 6 THEN _add_days ( current_date, - 13)
WHEN 7 THEN _add_days ( current_date, - 7)
END
Date item 2
as current_date
the result of the data item1 will be the date of previous week monday
and today date for dataitem2..
then in filter define it as
date between dataitem1 and dataitem2
which pulls date from previous week monday to today..
the other way to full as the blom said.. use
_week_of_year(current_date) - 1
and _day_of_week( current_date , 1 ) =1
and date = currentdate
which means, the week should be previous week and day is monday
bringing upto today
in this the 1st method s working for me.. havenot tried the 2nd but i got this idea later.. :)
Good luck dude..
venky