Need Avg$ of last 4 wks for any prompted week in year.

[CognosProfessional]

Ahoy! Anyone know of a way to get the Average dollars of the past 4 weeks for a prompted week chosen in a year in Impromptu?
This .iqd would then be used to build a cube that would be capable of choice to filter any of all the weeks of a year as options to filter data for a company's past 4 weeks average on that week chosen. Needs to display the projected credit for a price increase in regards to percentages from 1% to 10%.

here's the filter for the 4 weeks past for week chosen:
FROMDATE <=?Prompted date? and FROMDATE>=addmonths(?Prompted date?-1)

here's the calculation for the credit percentages col's:
1%=((total(FROMDATE)/4)*.01*Price)/1

The final Power Play WEB report would resemble something like this with the prompt of 5/11/02 chosen:

For the Week ending May 11, 2002:
Projected Credit for a Price Increase:
ABC Inc. 1% 2% 3% 4% 5% ... 10%
#7234 17.22 34.45 51.67 68.90 86.12 ...172.24
#7235 4.04 8.08 12.12 16.15 20.19 ... 40.38
DEF Inc.
#8325 11.35 22.70 34.05 45.40 56.75 ...113.51
#8443 3.57 7.14 10.72 14.29 17.86 ... 35.72
#8529 0.08 0.16 0.24 0.32 0.40 ... 0.79

My only answer is to create 52 .iqd's as a solution so far. I was wondering if anyone has a more efficient solution. With all of impromptu's functions using Oracle, maybe I am missing out on something that could reduce my whopping 52 .iqd's to a more managable number. IWR is not an option. This has to be a Power Play Web report. Any ideas are welcome...
Thanks,
CP

 

[griffindm]

CP,

Just a little mind drivel here. I thought I might have a solution for a moment, and then I realized that you will need for any given database row to contribute to 4 different averages, (i.e 4 possible weeks (+/-) per month) in the report. I've never found a way to have a single data row contribute to an two different aggregations that were not nested within a single report or iqd. Any thoughts on this?

Dave Griffin

 

[CognosProfessional]

Correction: the above calculation for the percentage columns:

1%=((total(InventoryQty)/4)*.01*Price)/1
2%=((total(InventoryQty)/4)*.02*Price)/1
3%=((total(InventoryQty)/4)*.03*Price)/1
4%=((total(InventoryQty)/4)*.04*Price)/1
5%=((total(InventoryQty)/4)*.05*Price)/1
6%=((total(InventoryQty)/4)*.06*Price)/1
7%=((total(InventoryQty)/4)*.07*Price)/1
8%=((total(InventoryQty)/4)*.08*Price)/1
9%=((total(InventoryQty)/4)*.09*Price)/1
10%=((total(InventoryQty)/4)*.10*Price)/1

Thanks anyway Dave, you are right. Just looking for new answers with functions to possibly lead me to other solutions. Wishful thinking on my part. ;-)

 

[griffindm]

CP,

Actually I had some more insight on this overnight. Rather than 52 queries, you could make do with only 4. In each of the reports a week would only be in one grouping. The first one would run based on the first desired week, and would calculate your averages for that week and every prior 4th week, using a grouping function based on reducing every date to the first week 'seed' date based on the addmonth function. Thus this report would give you the values for weeks 1,5,9,13, and so one. The next three reports would offset that starting week by 7, 14, and 21 days, and give you the values for weeks 2,6,10,14 etc for the second reports, weeks 3,7,11,15 for the third, and week 4,8,12,16, etc for the forth. Together they would give you the complete history of averages, and only have four static reports to manage.

Do you think this might suffice?

Dave Griffin

 

[CognosProfessional]

Thanks a mil Dave,

Didn't mean to keep you up late that night, but we are using the &quot;moving - average&quot; function and managed to get it down to two iqd's!
One of the iqd's has the percentages for each by using the column that figures the moving - average amounts / 4.
This is working well.
Thanks!
CP