topic Re: 4 Week Calculation - automatic update in QlikView

4 Week Calculation - automatic update

Mon, 11 Mar 2013 04:22:50 GMT

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.

Re: 4 Week Calculation - automatic update

sushil353 — Mon, 11 Mar 2013 05:06:37 GMT

try this:

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

Also find attached sample app

Re: 4 Week Calculation - automatic update

Mon, 11 Mar 2013 13:47:25 GMT

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

Re: 4 Week Calculation - automatic update

sushil353 — Mon, 11 Mar 2013 13:52:59 GMT

are u using personal edition?

Re: 4 Week Calculation - automatic update

Mon, 11 Mar 2013 13:58:13 GMT

Yes

Re: 4 Week Calculation - automatic update

sushil353 — Mon, 11 Mar 2013 14:01:35 GMT

Please find the attached..

HTH

Sushil

Re: 4 Week Calculation - automatic update

Mon, 11 Mar 2013 14:09:10 GMT

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

Re: 4 Week Calculation - automatic update

sushil353 — Mon, 11 Mar 2013 17:34:34 GMT

Can u provide the sample data?

Re: 4 Week Calculation - automatic update

Mon, 11 Mar 2013 18:58:20 GMT

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)