Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
It would be much easier if you transformed your data with The Crosstable Load.
- Marcus
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)
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
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 🙂
You're welcome.
Please close your thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco