topic Re: Count for Last Month in QlikView

Count for Last Month

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... Is there any solution with Set Analysis?

Do you know how could I do that?

Thank you!

Re: Count for Last Month

prat1507 — Thu, 16 Feb 2017 05:09:57 GMT

Use the below expression

=Count({<STATUS-={'Closed'}>}distinct(ID_TCKT))

Regards

Pratyush

Re: Count for Last Month

aarkay29 — Thu, 16 Feb 2017 05:30:06 GMT

May be this

count(distinct{<STATUS={'*'}-{'Closed'}>}ID_TCKT)

Re: Count for Last Month

Anonymous — Thu, 16 Feb 2017 06:00:03 GMT

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

Re: Count for Last Month

sasikanth — Thu, 16 Feb 2017 06:59:21 GMT

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.

Re: Count for Last Month

ganeshsvm — Thu, 16 Feb 2017 07:16:43 GMT

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)