using avg(aggr(max to build out 30 day average

[1600eads]

Hello Qlik gurus,

I'm stuck on a set analysis equation trying to find the aggregate max average for the last 30 days based on customers date and customer_id.

I have 3 fields: TIME_IN_QUEUE, CUSTOMER_ID, and CUSTOMER_DATE

I have my regular avg expression working fine

avg(aggr(max(TIME_IN_QUEUE), CUSTOMER_ID))

Now, I am trying to find the last 30 day avg using this same expression which is where I'm having issues.

I know the structure should look something like this - Aggr(Expression with Set analysis, Dimension)

avg(aggr({<CUSTOMER_DATE={"$(=today(),'MM/DD/YYYY')-30"}>} max(TIME_IN_QUEUE), CUSTOMER_ID))

This is returning null values and wondering if someone can point me on where I'm going wrong with this expression.

Thank you.

[David_Friend]

@1600eads 

To find the aggregate maximum average for the last 30 days based on customer date and customer ID, you can use the following expression:

avg(aggr(max(if(date(CUSTOMER_DATE) >= today() - 30, TIME_IN_QUEUE)), CUSTOMER_ID))

This expression calculates the maximum TIME_IN_QUEUE value for each CUSTOMER_ID, considering only the records where the CUSTOMER_DATE is within the last 30 days from today's date. The if() function checks if the CUSTOMER_DATE is greater than or equal to today's date minus 30 days, and if true, it includes the TIME_IN_QUEUE value in the calculation.

The max() function finds the maximum TIME_IN_QUEUE value for each CUSTOMER_ID within the specified date range. The aggr() function groups the calculation by CUSTOMER_ID, and the outer avg() function calculates the average of the maximum values across all CUSTOMER_IDs.

[David_Friend]

I used our A.I. assistant to help generate that answer as I don't do app development myself, you could also cross check using another source like ChatGPT etc.

[1600eads]

Thank you for your answer.

Might be a silly question but what if I wanted to implement 30 days from whichever date I select?

Lets say if I select 08/01/2024, I'd expect it to return me the last 30 days from the 08/01/2024 date. 

I think this is where I was trying to add the date as a set analysis vs using an if statement. 

CUSTOMER_DATE= {">=$(=Date(Max(CUSTOMER_DATE)-30))

which should allow the expression be dynamic based on a selection but I think this is where I'm having issues.

[Kushal_Chawda]

@1600eads  I don't think set analysis will work here as it involves multiple aggregation. You can try below

=avg(aggr(if(CUSTOMER_DATE >= max(total CUSTOMER_DATE) -30, max(total <CUSTOMER_ID> TIME_SPENT)), CUSTOMER_ID,CUSTOMER_DATE))

[1600eads]

It seems to be way overstated. The expression itself worked but that number shouldn't be that high. 

The idea is whenever a user selects any date on the 2nd column, I'll be able to display the increase or decrease from that date - 30 days. My current avg expression is working as expected but seems interestingly difficult to implement in my expression. My next action was to do Month2 - Month1 / Month 1 *100. 

[Kushal_Chawda]

@1600eads  It will no work as you are using the dimension CUSTOMER_DATE in your chart. Because your 30 days avg itself has condition on CUSTOMER_DATE. If use it as dimension it will not aggregate based on 30 days which will give you wrong results. Remove CUSTOMER_DATE from dimension and instead include CUSTOMER_ID