qlikviewset-analysis

Qlikview Set Analysis Average of the Minimum

I am trying to get the average of the minimum dates in a qlikview set analysis.

There is a set of data with an Initial Contact date, which is then followed by many Meeting Dates.

What I wanted to know was the average days from the InitialContact to the Min(MeetingDate) for all customers.

Ive had a look around the site and havnt seen anything comparable, I have also tried the online automatic set expression creator tool, however it doesnt have an example that works in this way.

All help is appreciated.

Cheers

Gary

Solution

  • I don't think a Set Analysis is what you are after. An aggr() should do what you want. I made this pretend data based on what you said

    LOAD *,date(num(date#(IC,'YYYY/MM/DD'))) as InitialContact INLINE [
CustomerNo, IC
1, 2016/08/28
2, 2016/08/29
3, 2016/08/30
];

LOAD *,date(num(date#(MD,'YYYY/MM/DD'))) as MeetingDate INLINE [
CustomerNo, MD
1, 2016/08/30
1, 2016/08/31
2, 2016/08/31
1, 2016/09/01
2, 2016/09/01
1, 2016/09/02
2, 2016/09/02
3, 2016/09/02
1, 2016/09/03
2, 2016/09/03
3, 2016/09/03
2, 2016/09/04
3, 2016/09/04
3, 2016/09/05
3, 2016/09/06
];

    And then I can use this expression 

     =avg(aggr(min(MeetingDate)-min(InitialContact),CustomerNo))

    to get the average time from Initial Contact to First Meeting