Average of min(date) with set analysis using dimension

[SkitzN27]

Hi

Here's my data 

The calculation for my Days column is = 'Date 1 - min({<Dimension={'XYZ'}>}Date2)' . Now I want to take an Average(Days) i.e. '(Average(Date 1 - min({<Dimension={'XYZ'}>}Date2)) 'and present it on bar chart with Yr Qtr of Date1 as my dimension.

Dimension: Yr Qtr of Date 1

Expression: Avg(Date 1 - min({<Dimension={'xyz'}>}Date2))

For example: The average for 2020 Q1 = (54+33+48)/3 = 45

The average of 2020 Q2 = (145+28)/2 = 86.5

@QFabian @MayilVahanan @rwunderlich 


Sample Data:

Unique Number	Phase	Days	Date1	Yr Qtr of Date 1	Dimension	min({<Dimension={'XYZ'}>}Date2)
1	Phase 1	-	-	-	xyz	01-Oct-2020
2	Phase 1	145	29-Jun-2020	2020 Q2	xyz	04-Feb-2020
3	Phase 1	54	30-Mar-2020	2020 Q1	xyz	04-Feb-2020
4	Phase 1	33	09-Mar-2020	2020 Q1	xyz	04-Feb-2020
5	Phase 1	-	-	-	xyz	15-Oct-2020
6	Phase 1	-	-	-	xyz	01-Oct-2020
7	Phase 1	-	-	-	xyz	01-Oct-2020
8	Phase 1	28	04-Jun-2020	2020 Q2	xyz	06-May-2020
9	Phase 1	48	24-Mar-2020	2020 Q1	xyz	04-Feb-2020

[QFabian]

aggr should be inside :

avg(aggr((min({<Dimension={'xyz'}>}date1), xyz) - date2) 

[MayilVahanan]

HI @SkitzN27 
When you are using Date1 & Date2 as dimension, 
min(Date1) always same as Date1 values (dimension).

May be, try like below
Avg(Aggr(Min({<Dimension={'xyz'}>}Total Date1) - Date2, Dimension))
--
Min({<Dimension={'xyz'}>}Total Date1) -- It assign the Min Date for all rows and subtract from Date2 based on Dim and takes avg.

[SkitzN27]

Hi guys @MayilVahanan  @QFabian 

None of the solutions seem to be working.
Let me put it in easier words:

Avg(if( Dimension = 'xyz' , min( Date1) -Date2, null()))

[Kushal_Chawda]

@SkitzN27  try below

= if( Dimension = 'xyz' , min(total <Dimension>Date1)- Date2,null())

[Kushal_Chawda]

@SkitzN27  or below

= avg(aggr(if( Dimension = 'xyz' , min(total <Dimension>Date1)- Date2,null()),Dimension))

[SkitzN27]

Hi @Kushal_Chawda  I have changed the post, can you look at it again?
I have provided all the data that might be needed

@MayilVahanan  @QFabian 

[QFabian]

i just did this : avg (total <[Yr Qtr of Date 1]> Days), try it please

[SkitzN27]

You can't directly put days in your expression.
Days is a calculated column that you get using   'Date 1 - min({<Dimension={'XYZ'}>}Date2)'

So you cant use Avg and Min together

@QFabian 

[QFabian]

where is Date2?