Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

cbaqirdhds
Contributor II

Set Analysis Syntax

My OLD Chart is not showing the right date on the X-Axis. When I update it in the WIP chart, I lose my look back of 8 weeks. How do I update both expressions to only show the last 8 weeks like the OLD chart and still include the logic from the old expressions on PROCESS_STEP = {'Submitted to Routing', 'Routed'},  IS_IN_PROD = {'Y'}?

An example if DEFECT_ID = 9229 and it should show Avg Days =  2 and Ticket = 1 but without having to select PROCESS_STEP.

1-3-2018 2-22-49 PM.jpg

OLD and incorrect Dimension: =date(WEEK_START, 'MM-DD-YY')

OLD Expression for # Tickets:  (Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>} DISTINCT
DEFECT_ID))

OLD Expression for Avg Days: sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
PROCESS_STEP_START_DATE)
,
DEFECT_ID))/
(
Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>} DISTINCT
DEFECT_ID))

1-3-2018 2-16-11 PM.jpg

New Dimension: =weekstart(PROCESS_STEP_START_DATE)

Expression for # Tickets:

Expression for Avg Days:

1-3-2018 2-16-23 PM.jpg

Tags (1)
12 Replies

Re: Set Analysis Syntax

WIP Chart doesn't seem to have any set analysis to show 8 weeks... not sure what you are hoping for?

cbaqirdhds
Contributor II

Re: Set Analysis Syntax

I want that chart to show 8 weeks along with the hard coded options from old including PROCESS_STEP = {'Submitted to Routing', 'Routed'}, IS_IN_PROD = {'Y'}, and the Max date logic. I tried just switching out MONTH_1 for PROCESS_STEP_START_DATE but that didn't fix it.

Re: Set Analysis Syntax

Can you share what you tried?

cbaqirdhds
Contributor II

Re: Set Analysis Syntax

sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
PROCESS_STEP_START_DATE)
,
DEFECT_ID))
/(
Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
DISTINCT
DEFECT_ID))

Re: Set Analysis Syntax

Can you rerun the app after creating a new field in the script

WeekStart(PROCESS_STEP_START_DATE) as WeekStart_PROCESS_STEP_START_DATE

cbaqirdhds
Contributor II

Re: Set Analysis Syntax

Updated

cbaqirdhds
Contributor II

Re: Set Analysis Syntax

I fixed part of it but broke another... If I use this for the Avg # days, I see 8 weeks but the PROCESS_STEP and IS_IN_PROD selections are not hardcoded. Any ideas? Something to do with the dimension?

sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}

,
PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
PROCESS_STEP_START_DATE)
,
DEFECT_ID))
/(
Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,
PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
DISTINCT
DEFECT_ID))

Re: Set Analysis Syntax

Where do you see it working in the attached application? I don't see it working in any of the charts from the attached application

cbaqirdhds
Contributor II

Re: Set Analysis Syntax

I uploaded a new version with the code in the last message. That's the closest I can get.

Community Browser