Count dates

[annanirvin]

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

[oknotsen · Accepted solution]

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.

[tresesco]

May be something like:

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

[annanirvin]

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

[annanirvin]

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

[oknotsen]

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,

[annanirvin]

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