If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to calclaulate week varience

Started by krishdw85, 31 Dec 2011 10:06:55 PM

Previous topic - Next topic

krishdw85

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!!!

blom0344

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?

venkys4u_

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