Count for Last Month

[microwin88x]

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!

[sunny_talwar]

Would you be able to share an app where the data is already loaded?

[microwin88x]

Hello Sunny,

What I have in my script is the following:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

DATA:

LOAD * INLINE [

    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

];

If I select for example MONTH_CALENDAR = Oct, I should count all ID_TICKETS with STATUS<>Closed for MONTH_STATUS=Sep, in this case I should get only 1, TCKT05, because it is In Progress (TCKT02 and TCKT03 are Closed).

Could you have a look please?

Thank you!

[sunny_talwar]

Is this how your raw data is laid out?

[microwin88x]

Yes, I'm having it like that so far