30 days average not working

[#1]

Hi

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.

[kaushiknsolanki]

Hi,

    Please have a look at the attached Doc.

Regards,

Kaushik Solanki

[#3]

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.

[kaushiknsolanki]

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