Hi All,
We have a report where the requirement is to pull in the previous 13 weeks of data based on the date the report is run on. So if the report is run today, 13 columns would be returned providing hours worked by user per week starting with the previous week.
I have the 13 columns set up using a Query Calculation and cannot seem to get the hours to correctly total.
The hours worked are from Saturday to Friday (i.e. 2/8 to 2/14) and here are two expressions I have tried, but neither are providing the correct hours worked. Some just total everything for a number of weeks giving a really high number.
total (case when
[AVIEW - AB].[A OBJECT 1].[dateAdded]
between
_add_days ( current_date, 7 - (_day_of_week ( current_date ,6 )) - 7) and
_add_days ( current_date, 7 - (_day_of_week ( current_date ,6 )) - 1)
then
[AVIEW - AB].[A OBJECT 1].[float2]
end)
------------------------------------------------------------------------
total (case when
[AVIEW - AB].[A OBJECT 1].[dateAdded]
between
_add_days(current_date,-7-_day_of_week(current_date,1))
and
_add_days(current_date,-_day_of_week(current_date,1))
then
[AVIEW - AB].[A OBJECT 1].[float2]
end)
Neither is working properly
What we are going for is something like this
Name Title 2/14 2/7 1/31
Joe Sr 40 38 40
Jane Sr 40 40 40
Mike Rep 8 6 6
I would also like to get a better understand on pulling previous weeks data as we have additional reports coming soon requiring this same functionality.
Thanks,
KS
I would test the individual date calculations first--that is, pull out the _add_days() bits into individual data items to see what they're outputting. Running your date calculations today (Fri, 2/21) gives range 2/14-20 (Fri-Thu) for the first expression, and range 2/9-16 (Sun-Sun) for the second, neither of which is what you want.
Do you have the next_day() function available? I think that would be the simplest way to get the appropriate Sat/Fri dates, but it's Oracle so you might not have it (Vendor Specific Functions/Oracle).
I would use crosstab instead of 13 calculations for every week.
Filter your Query to get last 13 weeks.
Create a calculation [Week Number] to map relevant date field to week number.
This way you can easily build a report with any number of weeks or as of any date.
Please reply if you need more details.