Hello,
I have the following table:
|
| TCKT05 | In Progress | 2016 | Jul | 15/07/2016 | 14:29:33 | Jul |
| TCKT05 | In Progress | 2016 | Aug | | | Jul |
| TCKT05 | In Progress | 2016 | Sep | | | Jul |
| TCKT05 | Closed | 2016 | Oct | | | Jul |
| TCKT02 | In Progress | 2016 | May | 02/05/2016 | 19:51:00 | May |
| TCKT02 | In Progress | 2016 | Jun | | | May |
| TCKT02 | Completed | 2016 | Jul | | | May |
| TCKT02 | Completed | 2016 | Aug | | | May |
| TCKT02 | Closed | 2016 | Sep | | | May |
| TCKT02 | Closed | 2016 | Oct | | | May |
| TCKT03 | Planned | 2016 | May | 20/05/2016 | 02:45:53 | May |
| TCKT03 | Planned | 2016 | Jun | | | May |
| TCKT03 | Closed | 2016 | Jul | | | May |
| TCKT03 | Closed | 2016 | Aug | | | May |
| TCKT03 | Closed | 2016 | Sep | | | May |
| TCKT07 | In Progress | 2016 | Oct | 15/10/2016 | 12:35:48 | Oct |
Each ID_TCKT has a CREATE_DATE/CREATE_TIME associated to a Master Calendar by a field MONTH_CALENDAR, so if I choose month = Jul I see TCKT05, if I choose month = May I see TCKT02 and TCKT03. Each ticket appear once, depending on selected month.
The issue is that I need to add the following logic:
If I choose any month, for example, MONTH_CALENDAR = Jun I need to COUNT how many tickets have STATUS <> Closed until the previous month from MONTH_STATUS field, in this case: MONTH_STATUS = May and this is what I should get, by using a Text Object:
- TCKT05: 0 (Because the ticket was Created in Jul)
- TCKT02: 1 (Because the ticket was In Progress in May)
- TCKT03: 1 (Because the ticket was Planned in May)
- TCKT07: 0 (Because the ticket was Created in Oct)
So the total COUNT would be = 2
Another example, if MONTH_CALENDAR = Oct we have to count tickets with STATUS <> Closed until MONTH_STATUS = Sep:
- TCKT05: 1 (Because the ticket is In Progress in Sep)
- TCKT02: 0 (Because the ticket was Closed in Sep)
- TCKT03: 0 (Because the ticket was Closed in Sep)
- TCKT07: 0 (Because the ticket was Created in Oct)
So the total COUNT would be = 1
I tried to use Set Analysis but I couldn't get what I need... Is there any solution with Set Analysis?
Do you know how could I do that?
Thank you!
