

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count for Last Month
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!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Use the below expression
=Count({<STATUS-={'Closed'}>}distinct(ID_TCKT))
Regards
Pratyush

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
count(distinct{<STATUS={'*'}-{'Closed'}>}ID_TCKT)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
