4 Week Calculation - automatic update

[#1]

I'm looking for formula in edit expression where I could get automatic update of current 4 weeks once new week becomes available.

I know how to create date ranges that are not dynamic, I'm looking for dynamic update.

i.e if I have weeks 1/18/13' & 1/25/13' & 2/1/13' & 2/8/13' = Current 4, I need automatic calculation to update my Current 4 Week

calculation once week 2/15/13' becomes available.

[sushil353]

try this:

sum({<weekdate={">=$(=date(max(weekdate)-21)) <=$(=date(max(weekdate)))"}>}data).

Also find attached sample app

[#3]

Your attached file doesn't seem to have any data in it

[sushil353]

are u using personal edition?

[#5]

Yes

[sushil353]

Please find the attached..

HTH

Sushil

[#7]

I can see it now, but I think I'm looking for something else. If you had one more dimension such as Region, and you wanted to see C4 (Current 4 weeks) of data, vs. P4 (Previous 4 weeks) by Region

I really don't want to see dates, just two "buckets" of C4 & P4 by Region i.e below

Region                C4         P4

Region A              15         20

Region B              17         25

Thanks

[sushil353]

Can u provide the sample data?

[#9]

I've got my answer through my boss - "Thank You boss!"

Thanks for your help. Here is what I got for current. If you want to go with previous you'd need to subctract instead of 21, 49 than 28 after [Week Ending Date]

Best,

Rafa

sum

({$<[Week Ending Date]={'>=$(=date(max([Week Ending Date])-21,"MM/DD/YYYY"))<=$(=date(max([Week Ending Date]),"MM/DD/YYYY"))'},[Product]={'TWA1'},
[Type]={'RETA'},[Payer]={'ABC'}>}Sales)