Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table:
ID_TCKT | STATUS | YEAR | MONTH_STATUS | CREATE_DATE | CREATE_TIME | MONTH_CALENDAR |
---|---|---|---|---|---|---|
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!
Hi
Use the below expression
=Count({<STATUS-={'Closed'}>}distinct(ID_TCKT))
Regards
Pratyush
May be this
count(distinct{<STATUS={'*'}-{'Closed'}>}ID_TCKT)
Hi Micro,
Please find the application in the attachments. I have developed the logic by using the Set Analysis. This application will help to solve your problem.
Thanks & Warm regards,
Venkata Sreekanth
HI try this,
Create a YearMonth field like 201601,201602 etc in both tables i.e master calendar and Existing table.
CLNDRMonthYear, TableMonthYear
now try below expression
=Count({<Status={'*'}-{'Closed'}, TableMonthYear={ "<= $(=Max(CLNDRMonthYear)-1)"}>}TICKET_ID)
I Assumed there is a separate master Calendar, if nor please share your Application.
Hope the following expression helps to get your desired results. I've tried frm my end and I'm getting the desired results, try and let me know.
=Count({1<STATUS-={'Closed'}, MONTH_STATUS={"$(=Month(MONTH_CALENDAR-1))"} >}DISTINCT ID_TCKT)