topic Re: 30 days average not working in QlikView

30 days average not working

Wed, 25 May 2011 10:10:26 GMT

I am trying to calculate a the previous 30 day average for any date selected ( average ranging from the max date selected to the previsous 30days) .

However my formula keeps giving me only the average for the number of days selected, not the 30 day average.

This is the formula I am using:

=(Count($<[Volume Flag]={'1'}, Business_Date+={"<$(=date(max(Business_Date),'dd-MMM-yy'))>$(=date((max(Business_Date)-31),'dd-MMM-yy'))"}>} Trade_ID))

/(Count($<Business_Date+={"<$(=date(max(Business_Date),'dd-MMM-yy')) >$(=date((max(Business_Date)-31),'dd-MMM-yy'))"}>}distinct Business_Date)

Thanks in advance for any help you can provide.

Re: 30 days average not working

kaushiknsolanki — Wed, 25 May 2011 11:01:01 GMT

Hi,

Please have a look at the attached Doc.

Regards,

Kaushik Solanki

Re: 30 days average not working

Wed, 25 May 2011 12:07:41 GMT

I tried the attached example but it still seems to not be working.

For the count of the days (as weekends and bankholidays don't have data) I used:

=Count({$<Business_Date={"<=$(v30DayAvgED)>$(v30DayAvgSD)"}>} distinct Business_Date)

Whereby:

v30DayAvgED = date(max(Business_Date), 'dd-MMM-yy')

v30DayAvgSD = date((max(Business_Date)-31), 'dd-MMM-YY')

However this is giving me 0.

Re: 30 days average not working

kaushiknsolanki — Wed, 25 May 2011 12:17:28 GMT

Hi,

Its works at my side, with same varibale expression you have used.

Tell me one thing have u created a year and month field using this date.

If yes then you have to exclude the selection of them too.

For example

Sum ({$<Year=,Month=,Date1 = {">=$(vPre)<=$(vSel)"}>}Value)

Regards,

Kaushik Solanki