Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SkitzN27
Creator
Creator

Average of min(date) with set analysis using dimension

Hi

Here's my data 

Screen Shot 2020-11-10 at 10.44.20 AM.png

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 NumberPhaseDaysDate1Yr Qtr of Date 1Dimensionmin({<Dimension={'XYZ'}>}Date2)
1Phase 1---xyz01-Oct-2020
2Phase 114529-Jun-20202020 Q2xyz04-Feb-2020
3Phase 15430-Mar-20202020 Q1xyz04-Feb-2020
4Phase 13309-Mar-20202020 Q1xyz04-Feb-2020
5Phase 1---xyz15-Oct-2020
6Phase 1---xyz01-Oct-2020
7Phase 1---xyz01-Oct-2020
8Phase 12804-Jun-20202020 Q2xyz06-May-2020
9Phase 14824-Mar-20202020 Q1xyz04-Feb-2020
14 Replies
SkitzN27
Creator
Creator
Author

The last column is date 2, I have just modified it using this formula: min({<Dimension={'XYZ'}>}Date2)

@QFabian 

 

Kushal_Chawda

@SkitzN27  try below

=Avg(aggr(Only(Date1)-min({<Dimension={'XYZ'}>}Date2),Date1,[Yr Qtr of Date 1]))

Kushal_Chawda

@SkitzN27  or may be below. 

=Avg(aggr(Only(Date1)- min(if(Dimension='xyz',Date2)),Date1,[Yr Qtr of Date 1]))

MayilVahanan

Hi @SkitzN27 

Try like below

In bar chart.
Avg(Aggr(Date1 - [min({<Dimension={'XYZ'}>}Date2)], [Yr Qtr of Date 1], [Unique Number]))

or
In Straight table
Avg(Total<[Yr Qtr of Date 1]> Aggr(Date1 - [min({<Dimension={'XYZ'}>}Date2)], [Yr Qtr of Date 1], [Unique Number]))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
SkitzN27
Creator
Creator
Author

Thank you so much @MayilVahanan