aggregating Data 1

[ridhirao22]

Using CR 2008, Oracle 11g:

We have a report with lot of metrics to show by day (fiscal calendar current year vs last year) and aggregate by WTD, MTD, QTD, and YTD.
How can I aggregate last year data for WTD(week to date) only instead of full week? The report what I have now is aggregating for the whole week for last year. Grouping is done by the calender table we have where it has week number, month numbers, qtr number and year( for ex: 201209, 201202,20121,2012)
Below is the sample it should add up like

Dates Current Year Last Year
3/25/2012 2107.45 1423.75
3/26/2012 1734 2438.02
3/27/2012 1691.97 1710.08
3/28/2012 0 2340.94
3/29/2012 0 1908.06
3/30/2012 0 2073.5
3/31/2012 0 1547.53

WTD 5533.42 5571.85



Thank you in advance!
RR

 

[lbass]

What formula are you using to calculate "last year"--please show the content.

-LB

 

[ridhirao22]

I am not using any. Currently, it just adds up for whole week. I am trying to find a way to add only until sysdate for last year as in above ex:

Thank you,
RR

 

[lbass]

Replace {table.lastyear} with a formula:

if {table.lastyear} <= {table.thisyear} then
{table.lastyear}

This assumes that {table.lastyear} and {table.thisyear} are date type fields.

-LB

 

[lbass]

Sorry, that made no sense. Guess I'm tired.

The formula should be:

//{@lastyr}:
if {table.date} <= currentdate then
{table.lastyear}

This assumes {table.lastyear} is returning last year's amounts for the specific date.

-LB

 

[ridhirao22]

Hmm.. Wish I could explain better.

I am doing running total and it works, but have to create lot of them I guess it would be same with formulas and grouping too.

Thank you very much LB. Really appreciate all the help you do taking time your out for people like me.

RR

 

[lbass]

Why are you using running totals? How are they set up? Usually it is better to use the more usual inserted summaries if you can, so please explain how one of the running totals is set up.

-LB

 

[ridhirao22]

Select * from temptable ( has data for this year and last year)
Command (select day,week,per,qtr,year from cal where day = sysdate)
The command gets me the current week,per, qtr,year

The report is grouped by day, week, period, qtr, year
Suppress logic for Week GF is temptable.week = command.week
Suppress logic for period GF is temptable.per = command.per


The data comparsion for this year vs last year. So I need to aggregate the data for the WTD, PTD, QTD, YTD
Say the report run on Monday. I wouldn’t have data for this year for the rest of the week except Monday, whereas for last year I would and the it aggregates for the full week for last year data.

This is what I am doing in the running total.
Sum LY Amount, evaluate by formula
@ If temptable.week = command.week then
Temptable.day < command.day

Reset – never

I was looking to do something without running totals since there are lots of formula to deal with. Any ideas are appreciated

TIA,
RR

 

[lbass]

I think you can just replace your running totals with conditional formulas like this:

If temptable.week = command.week and
Temptable.day <= command.day then //I think this should be <=
{@LY Amount}

Don't know the content of {@LY Amount}, but if it doesn't reference next/previous or any summaries, you should be able to place the formula in the detail section and insert summaries on it at whatever level you need (versus creating one rt per group level). If you are doing any counts or distinctcounts on a conditional formula, though, be sure to set them up like this:

if {table.field}="A" then
{table.ID} else
tonumber ({@null})

...where {@null} is a new formula that you open and save without entering anything. This will prevent records that don't meet the criterion from being counted, as would happen if you simply allowed the default value (0 for a number, or "" for a string). Note that you must convert {@null} to the correct datatype (date()) or tonumber()) unless the formula returns a string.

-LB

 

[ridhirao22]

LB, Thanks for the idea. I got stuck with somethin else and took me so long to reply. sorry about that!

when I did the conditional formulas. I was all ready do a sum(field,group) and it was printing zero. butI follwed you suggestion to place in the detail and sum. It worked like a charm.

I will replace all the running totals to conditional formulas.

I am encountring another issue show the aggrated data for a week to date.

I have a grouped the report by day, week, month qtr. and year.

Report prints from year start to end of the current month. After each day until the end of the current month. I am try to get the summary for week to date, month to date, qtr to date and year to date.

I can I make the week to date print after the last day of the month. Current it prints for every current week for example:
Report like this:
Date Quantity Amount
4/5/2012 10 $90
4/6/2012 12 $80
4/7/2012 9 $50
4/8/2012 6 $40
4/9/2012 25 $140
4/10/2012 20 $130
4/11/2012 23 $136
4/12/2012
4/13/2012
4/14/2012
WTD 74 $446
4/15/2012
4/16/2012
so on until end of month
4/30/2012
PTD 105 $666

I want to see the report print like this:

Date Quantity Amount
4/5/2012 10 $90
4/6/2012 12 $80
4/7/2012 9 $50
4/8/2012 6 $40
4/9/2012 25 $140
4/10/2012 20 $130
4/11/2012 23 $136
4/12/2012
4/13/2012
4/14/2012
4/15/2012
4/16/2012
so on until end of month
4/30/2012
WTD 74 $446
PTD 105 $666

Any suggestions are greatly appreciated! Thank you very much as always.
RR

 

[lbass]

Use conditional formulas like this in the detail section and insert sums on them at the month group level:

//{@wtd}:
if {table.date} in weektodatefromsun then
{table.amt} //or {table.qty}

//{@monthtodate}:
if {table.date} in monthtodate then
{table.amt} //or {table.qty}

-LB

 

[ridhirao22]

Thanks a ton! LB