average between dates

[#1]

Dear Community,

I've got a problem where I want to sum activity in the past 13 weeks and then take an average per week. The issue is, I want the 13 week average to be taken from whenever the client last traded. So in one table, one client may have last traded on the 19/08/2016 so I sum up all activity from this date back 13 weeks. but another client may not have traded since 01/07/2016 so I want the 13 week average from that date.

I've tried using max(date)-(13*7) in the expression but Qlik doesn't like it.

Can anyone help?

count(distinct {<statusname={'Invoiced','Booking','Hold','Transferred','Complete'},[Budget Category]={'Actuals'}>}
if(collectdate>=max(collectdate)-(13*7) AND collectdate<=max(collectdate),quote))/13)

[swuehl]

You are trying to use an aggregation within another aggregation, which won't work this way.

Maybe you can use advanced aggregation to get your desired result, something like (assuming a quote shows a unique collectdate)

Sum( {<statusname={'Invoiced','Booking','Hold','Transferred','Complete'},[Budget Category]={'Actuals'}>}

     Aggr(

     if(collectdate>=max( {<statusname={'Invoiced','Booking','Hold','Transferred','Complete'},[Budget Category]={'Actuals'}>}Total<client> collectdate)-13*7)

          AND

        collectdate<=max( {<statusname={'Invoiced','Booking','Hold','Transferred','Complete'},[Budget Category]={'Actuals'}>}Total<client> collectdate)

      , 1)

     ,client, quote)

)

/ 13

[#3]

Hi Stefan,

Unfortunately the quote does not show a unique collect date.

the quote is a unique reference for a particular booking. we might have thousands of bookings collected on the same date. I tried your expression but unfortunately didn't get a result (just showing zeros).

Best regards,

Drew