topic Re: average between dates in QlikView

average between dates

Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: average between dates

swuehl — Fri, 19 Aug 2016 22:04:06 GMT

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

Re: average between dates

Tue, 23 Aug 2016 09:50:59 GMT

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