topic Re: Help needed! How to count past due dates from multiple selections? in QlikView

Help needed! How to count past due dates from multiple selections?

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

Hello All,

I am a beginner to QV and need some help.

I have 9 columns with heading due date1, due date 2, due date 3 and so on untill due date 9. i want to count the total number of due dates that are past todays date.

eg:

Team Name	Due Date 1	Due Date 2	Due Date 3	Due Date 4	Due Date 5	Due Date 6	Due Date 7	Due Date 8	Due Date 9
1	1 Apr 2013	1 Apr 2013	1 Apr 2013	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015
2	1 Apr 2014	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015
3	1 Aug 2013	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015
4	1 Feb 2014	1 Apr 2014	1 Apr 2014	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015
5	1 Dec 2016	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015	1 Aug 2015

=if([Due Date (1)]<=( today()),count(Ticket_No) + if([Due Date (2)]<=( today()),count(Ticket_No)

Re: Help needed! How to count past due dates from multiple selections?

marcus_sommer — Sat, 11 Feb 2017 14:38:30 GMT

It would be much easier if you transformed your data with The Crosstable Load.

- Marcus

Re: Help needed! How to count past due dates from multiple selections?

Gysbert_Wassenaar — Sat, 11 Feb 2017 15:16:49 GMT

As Marcus said it's a lot easier if you pivot your data first. If you do that and keep only one Due Date field you can use an expression like this:

count({<[Due Date]={"<=$(=Date(Today(),'D MMM YYYY'))"}>}Ticket_No)

If you don't want to or cannot do that you can try this:

count({<[Due Date 1]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 2]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 3]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 4]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 5]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 6]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 7]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 8]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+

<[Due Date 0]={"<=$(=Date(Today(),'D MMM YYYY'))"}>+}Ticket_No)

Re: Help needed! How to count past due dates from multiple selections?

MarcoWedel — Sun, 12 Feb 2017 00:49:20 GMT

Hi,

another solution might be:

=Count({$<[Due Date 1]={"=[Due Date 1]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 2]={"=[Due Date 2]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 3]={"=[Due Date 3]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 4]={"=[Due Date 4]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 5]={"=[Due Date 5]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 6]={"=[Due Date 6]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 7]={"=[Due Date 7]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 8]={"=[Due Date 8]<=$(vToday)"}>} [Team Name])+
Count({$<[Due Date 9]={"=[Due Date 9]<=$(vToday)"}>} [Team Name])

hope this helps

regards

Marco

Re: Help needed! How to count past due dates from multiple selections?

Anonymous — Wed, 15 Feb 2017 13:23:12 GMT

Thank you every one,

This is what worked for me, But i could not have arrived here if I didn't have the inputs that all of you gave. That was start point and then fiddled around for about an hour and arrived at the following.

=if([Due Date (1)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (1)])))+if([Due Date (2)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (2)])))+if([Due Date (3)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (3)])))+if([Due Date (4)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (4)])))+if([Due Date (5)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (5)])))+if([Due Date (6)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (6)])))+if([Due Date (7)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (7)])))+if([Due Date (8)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (8)])))+if([Due Date (9)],(Ticket_No)<=(today()),count(DISTINCT([Due Date (9)])))

Looking forward for more such interactions 🙂

Re: Help needed! How to count past due dates from multiple selections?

MarcoWedel — Wed, 15 Feb 2017 23:22:39 GMT

You're welcome.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco