topic Re: Count dates in QlikView

Count dates

annanirvin — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I'm new with Qlikview and I need a little bit of help.

For every OrderId I have 'OrderDate' and 'CriticalDate', where 'CriticalDate' is a future date. I need to know how many of my orders that will become 'critical' within 7 days as from Today ().

Anyone who can give me some advice?

Best regards

Anna

Re: Count dates

oknotsen — Tue, 08 Nov 2016 12:12:12 GMT

I would add a flag field to your table with 0 or 1 in it.

Something like this:

if(CriticalDate < Today()+7, 1, 0) as SoonCriticalFlag,

Next you can sum(SoonCriticalFlag) to get your answer.

Re: Count dates

tresB — Tue, 08 Nov 2016 12:17:46 GMT

May be something like:

Count( {<CriticalDate={'>=$(=Today()) <=$(=(Date(Today()+7)))'}>}Distinct OrderId )

Re: Count dates

annanirvin — Tue, 08 Nov 2016 17:32:10 GMT

Thanx, this works fine Do you know if it is possible to exclude dates that are already passed?

Re: Count dates

annanirvin — Tue, 08 Nov 2016 17:36:01 GMT

Thank you. Unfortunately it didn't work out but I appreciate your help anyway

Re: Count dates

oknotsen — Tue, 08 Nov 2016 21:23:16 GMT

Probably something like this:

if(CriticalDate < Today()+7, if(CriticalDate > Today(), 1, 0), 0) as SoonCriticalFlag,

That should only flag those CriticalDate's happening the next 7 days.

Maybe add a PastCriticalFlag?

if(CriticalDate < Today(), 1, 0) as PastCriticalFlag,

Re: Count dates

annanirvin — Wed, 09 Nov 2016 06:40:09 GMT

Thanks a lot, If(CriticalDate < Today()+7, if(CriticalDate > Today(), 1, 0), 0) as SoonCriticalFlag is just perfekt!