Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Count for Last Month

Hello,

I have the following table:

ID_TCKTSTATUSYEARMONTH_STATUSCREATE_DATECREATE_TIMEMONTH_CALENDAR
TCKT05In Progress2016Jul15/07/201614:29:33Jul
TCKT05In Progress2016AugJul
TCKT05In Progress2016SepJul
TCKT05Closed2016OctJul
TCKT02In Progress2016May02/05/201619:51:00May
TCKT02In Progress2016JunMay
TCKT02Completed2016JulMay
TCKT02Completed2016AugMay
TCKT02Closed2016SepMay
TCKT02Closed2016OctMay
TCKT03Planned2016May20/05/201602:45:53May
TCKT03Planned2016JunMay
TCKT03Closed2016JulMay
TCKT03Closed2016AugMay
TCKT03Closed2016SepMay
TCKT07In Progress2016Oct15/10/201612:35:48Oct

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!

5 Replies
prat1507
Specialist
Specialist

Hi

Use the below expression

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

Regards

Pratyush

aarkay29
Specialist
Specialist

May be this

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

Anonymous
Not applicable

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

sasikanth
Master
Master

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.

ganeshsvm
Creator II
Creator II

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)