Forum Discussion
Count IF based on date
Hello All - I have a scenario where I do a count IF based on below sample data table
1. Count IFS - Priority (P1,P2..) based on Open date & Closed date with situation = Issues ( should exclude action)
Trend I am trying to build
| Date | P1 Open | P2 Open | P3 Open | P4 Open | P5 Open | GLC Open | P1 Close | P2 Close | P3 Close | P4 Close | P5 Close | GLC Close |
| 25-Nov-18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 3 | 0 | 0 | 5 |
| 2-Dec-18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 3 | 0 | 0 | 5 |
| 9-Dec-18 | 0 | 12 | 8 | 1 | 0 | 20 | 0 | 5 | 3 | 0 | 0 | 8 |
| 16-Dec-18 | 0 | 21 | 16 | 1 | 1 | 37 | 0 | 12 | 10 | 0 | 0 | 22 |
| 23-Dec-18 | 0 | 24 | 26 | 2 | 1 | 50 | 0 | 17 | 14 | 0 | 0 | 31 |
| 30-Dec-18 | 0 | 28 | 27 | 2 | 1 | 55 | 0 | 20 | 16 | 0 | 0 | 36 |
| 6-Jan-19 | 0 | 34 | 32 | 4 | 3 | 66 | 0 | 25 | 23 | 0 | 0 | 48 |
| 13-Jan-19 | 0 | 41 | 41 | 4 | 4 | 82 | 0 | 30 | 27 | 1 | 0 | 57 |
| 20-Jan-19 | 0 | 52 | 49 | 7 | 5 | 101 | 0 | 44 | 36 | 1 | 0 | 80 |
| 27-Jan-19 | 0 | 72 | 59 | 7 | 5 | 131 | 0 | 56 | 46 | 1 | 0 | 102 |
| 3-Feb-19 | 0 | 88 | 69 | 11 | 5 | 157 | 0 | 75 | 54 | 3 | 0 | 129 |
| 10-Feb-19 | 0 | 94 | 82 | 13 | 8 | 176 | 0 | 88 | 66 | 6 | 3 | 154 |
| 17-Feb-19 | 0 | 97 | 92 | 15 | 8 | 189 | 0 | 92 | 76 | 6 | 3 | 168 |
| 24-Feb-19 | 0 | 102 | 100 | 17 | 8 | 202 | 0 | 96 | 81 | 6 | 3 | 177 |
| 3-Mar-19 | 0 | 104 | 100 | 18 | 8 | 204 | 0 | 98 | 81 | 6 | 3 | 179 |
| 10-Mar-19 | 0 | 118 | 111 | 20 | 8 | 229 | 0 | 111 | 96 | 6 | 3 | 207 |
| 17-Mar-19 | 0 | 139 | 121 | 21 | 8 | 260 | 0 | 118 | 103 | 6 | 3 | 221 |
| 24-Mar-19 | 1 | 153 | 133 | 22 | 9 | 287 | 1 | 140 | 112 | 6 | 3 | 253 |
| 31-Mar-19 | 1 | 169 | 156 | 24 | 9 | 326 | 1 | 150 | 129 | 7 | 4 | 280 |
Data Table
| Issue ID | Project | Situation | Description | Issue Status | Priority | Due Date | Open Date | Closed Date |
| 90919 | 4512 - RA Models - Secondary Launch | Issue | Incorrect warehouse return for RA-2051-DPL and RA-0078-ACC | In Process | P4 | 12/6/2018 0:00 | ||
| 91914 | 4512 - RA Models - Secondary Launch | Action | Ability of Global Trax to consolidate SOPO and IRISO orders | In Process | P3 | 4/3/2019 0:00 | 1/8/2019 0:00 | |
| 92349 | 4512 - RA Models - Secondary Launch | Issue | WSS OA is showing Weird values under configuration | Reported | P5 | 1/18/2019 0:00 | ||
| 94441 | 4512 - RA Models - Secondary Launch | Issue | Pudong sales office is unable to receive the items because there is no RA model displayed in RNI Packing slip | In Process | P2 | 3/21/2019 0:00 | 2/22/2019 0:00 | |
| 95190 | 4512 - RA Models - Secondary Launch | Issue | SMMC_GSOF Promise Date is not update based on SSD + PPLT | Closed | P4 | 3/25/2019 0:00 | 3/7/2019 0:00 | 3/7/2019 0:00 |
3 Replies
Hi sudanaditya ,
Sorry, I think I can't understand your requirement well. Could you please share the expected result to us?
Best Regards,
Teige
Hello -Let me create a simple table and explain it . In the below table the we are conting Priority for a date if the Issue category = Issue. Please find the Result table below as how it should display .
Thanks in advance
sudan
Open date Issue Category Priority 3/4/2019 Action P1 3/4/2019 Issue P1 3/4/2019 Issue P2 3/5/2019 Issue P2 3/6/2019 Issue P3 Please note Issue category -Action is excluded Result Opendate P1 P2 P3 P4 3/4/2019 1 1 3/5/2019 1 3/6/2019 1 3/7/2019 Any help is appreciated
