Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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 NameDue Date 1Due Date 2Due Date 3Due Date 4Due Date 5Due Date 6Due Date 7Due Date 8Due Date 9
1

1 Apr 2013

1 Apr 2013

1 Apr 20131 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 2015
21 Apr 2014

1 Aug 2015

1 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 2015
31 Aug 20131 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 2015
41 Feb 20141 Apr 20141 Apr 20141 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 2015
51 Dec 20161 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 20151 Aug 2015

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
marcus_sommer

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

- Marcus

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
MarcoWedel

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])

QlikCommunity_Thread_249439_Pic1.JPG

hope this helps

regards

Marco

Anonymous
Not applicable
Author

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 🙂

MarcoWedel

You're welcome.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco