Avg() inside Aggr() and tracking of status dates in Set Analysis

[valpassos]

Hi community!

Could someone please explain to me what exactly are the inner and outer AVG() function doing in the expression below?
How would you explain the expression in layman's terms?

=AVG(AGGR(AVG(Interval([End_Date] - [Start Date],'dd')),[%KEY_Product]))

Also, how would I put set analysis inside this AGGR()?
Imagine I'd like to calculate the duration in days it took for a product to get from ordered to shipped, and I have this status (ordered, packaged, shipped...) in a dimension/filter pane.
How would I put that in set analysis inside the AGGR()?

Thanks in advance for your time!

Lisa

[valpassos]

Anyone?

[Lauri]

The expression that you have is currently calculating the average number of days, from start to end, of each Key_Product and then taking the average of those averages. 

To use set analysis, you need to decide which dimensions are involved with which calculation. The first averaging, and the second? And don't forget the Aggr function needs it too, most likely (otherwise you could get unexpected results).

I'll make some assumptions:

1. You want the calculation to always be on items that have shipped
2. StartDate is when the Order happened
3. EndDate is when the Shipping happened
4. Your status dimension is called "Status"

Then the statement can be:

=AVG({<Status={'Shipped'}>} AGGR({<Status={'Shipped'}>} AVG({<Status={'Shipped'}>} Interval([End_Date] - [Start Date],'dd')),[%KEY_Product]))

It may well be overkill to put the set analysis in the outermost AVG in this situation, but I don't think it can hurt. 

[valpassos]

Hi @Lauri,

Thanks for your help!

I think that solution could work, if it weren't for something I forgot to mention: I have more than 2 status, and I may want to measure the time it took for the product to get from status 1 (ordered) to final state (shipped). But I have intermediary status, like "sorted", "packaged"... And each of these status as a Start Date and the End Date of each status corresponds to the Start Date of the next stage Start Date. So, it's something like this:

Stage 1: Ordered
Start Date: 01/09/2019
End Date: start date of the next stage -  02/09/2019

Stage 2: Sorted
Start Date: 02/09/2019
End Date: start date of the next stage - 04/09/09

Stage 3: Pre-packaged
Start Date: 04/09/2019
...

How can I measure the time in set analysis from the time it took for the product to go from stage 1 (Ordered) to stage, say, 3 (Pre-packaged)? And from stage 1 to the final stage(Shipped)?

Thanks!
Lisa

[Lauri · Accepted solution]

One option:

=AVG(AGGR(AVG(Interval(If(Status='Shipped', [End_Date]) - If(Status='Ordered', [Start Date]),'dd')),[%KEY_Product]))

We use If() instead of Set Analysis because we don't need to aggregate the dates. (I assume that each [%KEY_Product] has only one Start Date and one End Date for each Status.) Set Analysis must be inside of an aggregation function.

However, you probably want to make it more dynamic so that you can change between Shipped/Ordered and Shipped/Presorted, and all the other combinations of Status. In that case, you probably want to use Alternate States, which I'm not familiar with.