Count Tickets until Previous 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
TCKT03	Planned	2016	May	20/05/2016	02:45:53	May
TCKT03	Planned	2016	Jun			May
TCKT03	Closed	2016	Jul			May

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 Closed in Sep)

- TCKT03: 1 (Because the ticket was Closed in Jul)

So the total COUNT would be = 2

Another example, if MONTH_CALENDAR = Aug we have to count tickets with STATUS <> Closed until MONTH_STATUS = Jul:

- TCKT05: 1 (Because the ticket is In Progress in Jul)

- TCKT02: 1 (Because the ticket is Completed in Jul)

- TCKT03: 0 (Because the ticket was Closed in Jul)

So the total COUNT would also be = 2

I tried to use Set Analysis but I couldn't get what I need.

Do you know how could I do that?

Thank you!

[vinieme12]

Create numeric month fields if you don't have already and use the below expression

=count( {<NUM_MONTHStatus = {"<=$(=MIN(NUM_MONTHCal))"} , STATUS -={'Closed'} >}DISTINCT ID_TCKT)

LOAD ID_TCKT,

     STATUS,

     YEAR,

    MONTH_STATUS,

    NUM(Month(Date#(MONTH_STATUS, 'MMM')))  as NUM_MONTHStatus,

     CREATE_DATE,

     CREATE_TIME,

     MONTH_CALENDAR,

     NUM(Month(Date#(MONTH_CALENDAR, 'MMM')))  as NUM_MONTHCal

FROM

[https://community.qlik.com/thread/243734]

(html, codepage is 1252, embedded labels, table is @1);