avg between dates

[jerry_ile]

Hi,

I've read lots of posts but still can't crack this

I'm using Qlik Sense and would like a 4 week average on sales units

I have a variable for Current Week

=Max([Start Wk])  is set as  vCurrentStWk

My date field is [Start Wk]

I noticed it has a timestamp e.g. 25/06/2018 00:00:00 so I have tried the following but still had no joy

Avg({<date([Start Wk],'DD/MM/YYYY')={'>=$(vCurrentStWk)-28'}}>} [Sold Qty])

I would love some help

Thanks

[DavidŠtorek]

Hi,

I don't think it is necessary to change date format by Start Wk. It seems as a little problem with syntax (definitelly you have extra } in expression)

try it like following

Avg({<[Start Wk]={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])

[MK9885]

Try by removing time stamp from the date and then use it in the expression?

Since your expression just has date format not the timestamp format.. thats why it work?

[jerry_ile]

Thanks David but Still not working ;0(

I have tried loading date without the timestamp into a new field called StartWKNTS and setting that as the Variable to avoid the formatting issue

I noticed

=$(vCurrentStartWK) shows as 15/07/2018

but  =$(vCurrentStartWK)-28 shows as 43268

num(Date#(StartWkNTS,'DD/MM/YYYY')) shows as 43289

I tried variations on the following but still no Joy

Avg({<[StartWKNTS]={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])

Avg({<num(Date#(StartWkNTS,'DD/MM/YYYY'))={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])

Avg({<num(Date#(StartWkNTS,'DD/MM/YYYY'))={">=$(vCurrentStartWK)-28"}>} [Sold Qty])