Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
cbaqirdhds
Valued Contributor

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

Tags (2)
1 Solution

Accepted Solutions

Re: Rolling 12 Month Graph with hard coded field selections

They match now

16 Replies
cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

Re: 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:

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)

cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

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

cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

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

cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

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

Re: Rolling 12 Month Graph with hard coded field selections

Checking it now

Re: Rolling 12 Month Graph with hard coded field selections

Check the attached

cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

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!

cbaqirdhds
Valued Contributor

Re: Rolling 12 Month Graph with hard coded field selections

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