topic Re: Difference between two dates with aggr() in QlikView

Difference between two dates with aggr()

Fri, 16 Sep 2016 15:13:53 GMT

Hi all!

I'm having a problem while trying to read the difference between two dates

I am using the following formula:

"Interval(fabs(Date#(MQCREATED, 'DD/MM/YYYY hh:mm:ss'))

fabs(Date#(aggr(min(MQCREATED),APPT_NUMBER), 'DD/MM/YYYY hh:mm:ss')),'DD')"

But it doesn't show any result.

I've tested 2 things: changing "MQCREATED" for a date (Column 2) and changing "aggr(min(MQCREATED),APPT_NUMBER)" for a date (Column 3). In both cases I've got a result.

I'd like to do only "MQCREATED - aggr(min(MQCREATED),APPT_NUMBER)". Any thoughts ?

Re: Difference between two dates with aggr()

sunny_talwar — Fri, 16 Sep 2016 16:59:29 GMT

Would you be able to share a sample to look at?

Re: Difference between two dates with aggr()

Fri, 16 Sep 2016 19:48:23 GMT

Sure!

What I am trying to do is calculate the time difference between a specific occurrence and the first time this occurrence happened.

Thanks in advance.

Re: Difference between two dates with aggr()

swuehl — Fri, 16 Sep 2016 20:19:40 GMT

Maybe like

Re: Difference between two dates with aggr()

Fri, 16 Sep 2016 21:04:01 GMT

Hey Stefan!

I really liked your approach and yes, it works for my case.

Do you know why my approach was wrong ? Did I miss something ?

Thanks!!!

Re: Difference between two dates with aggr()

swuehl — Fri, 16 Sep 2016 21:17:35 GMT

In your dimension, you were mixing two different kind of granularities, row based vs. APPT_NUMBER.

I think this grain mismatch causes your issues.