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

sunny_talwar — Tue, 24 Jan 2017 13:27:01 GMT

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

Re: Count for Last Month

microwin88x — Tue, 24 Jan 2017 14:02:41 GMT

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!

Re: Count for Last Month

sunny_talwar — Tue, 24 Jan 2017 14:06:04 GMT

Is this how your raw data is laid out?

Re: Count for Last Month

microwin88x — Tue, 24 Jan 2017 14:11:04 GMT

Yes, I'm having it like that so far