Count Field for Previous Months

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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...

Do you know how could I do that?

Thank you!

Re: Count Field for Previous Months

vinieme12 — Mon, 19 Dec 2016 19:20:43 GMT

Please don't double post

Count Tickets until Previous Month

Re: Count Field for Previous Months

microwin88x — Mon, 19 Dec 2016 19:25:55 GMT

Hello. Is there any way to use the previous month here?

Because with MIN(NUM_MONTHCal) I get the one I selected:

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

Re: Count Field for Previous Months

vinieme12 — Mon, 19 Dec 2016 19:31:12 GMT

change the Set Expression to just < instead of <=

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

topic Re: Count Field for Previous Months in QlikView

Count Field for Previous Months

Re: Count Field for Previous Months

Re: Count Field for Previous Months

Re: Count Field for Previous Months