Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

Rolling 12 Month Graph with hard coded field selections

I am trying to take these 2 expressions and modify it to only show the previous 12 months (where CLOSE_DATE is within the last 12 months) and need some help:

Count:

Count({$<DEFECT_ID = {"=Count({<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>}PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}, PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} DISTINCT DEFECT_ID)

Avg Days:

SUM({$<DEFECT_ID *= {"=Min({$}CLOSE_DATE) >= AddMonths(Today(),-12)"},PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} AggrMax({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>}PROCESS_STEP_START_DATE) - Min({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP_START_DATE), DEFECT_ID)) /
(
Count({$<DEFECT_ID *= {"=Min({$}CLOSE_DATE) >= AddMonths(Today(),-12)"},PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'},
DEFECT_ID = {"=Count({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}>} DISTINCT DEFECT_ID))

The numbers in the bottom graphs should match the numbers in the top graphs:

8-30-2017 10-28-51 AM.jpg

Message was edited by: Cassandra Baqir

1 Solution

Accepted Solutions

16 Replies
Specialist
Specialist

I am trying to take these 2 expressions and modify it to only show the previous 12 months (where CLOSE_DATE is within the last 12 months) and need some help:

What do you mean when you say where CLOSE_DATE is within the last 12 months? What is the expected output based on the sample provided (with or without selections)

Specialist
Specialist

The expected output is what is in this image (also includes IS_IN_PROD = Y):

These two graphs are dependent on making two selections. The graphs I need help with have those selections hard-coded because I use them for Nprinting.

What I am looking for are tickets that were CLOSED in the specified month within the last 12 months. If we needed to add a date field, it would be CLOSE_DATE.

8-30-2017 10-18-42 AM.jpg

Specialist
Specialist

Edited image for expected output because I forgot that IS_IN_PROD was also hard coded.

Specialist
Specialist

I updated the QVW sample and screen shots. Does that make it more clear?

Checking it now

Check the attached

Specialist
Specialist

It's the bottom charts I am trying to see the rolling 12 months in. The ones with the process steps and is_in_prod hardcoded.

I will try adding MONTH_1 = {"$(='>=' & Date(MonthStart(Max(MONTH_1), -11), 'MM-YY') & '<' & Date(MonthStart(Max(MONTH_1), 1), 'MM-YY'))"} to them. Thanks!

Specialist
Specialist

How do we apply the same logic to the bottom Avg # Days chart with the values hardcoded?

SUM({$<DEFECT_ID *= {"=Min({$}PROCESS_STEP_START_DATE) >= AddMonths(Today(),-12)"},
PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} Aggr( Max({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>}PROCESS_STEP_START_DATE) - Min({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP_START_DATE), DEFECT_ID)) /
(Count({$<DEFECT_ID *= {"=Min({$}PROCESS_STEP_START_DATE) >= AddMonths(Today(),-12)"},
PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'},
DEFECT_ID = {"=Count({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}>} DISTINCT DEFECT_ID))