Set Analysis Syntax

[cbaqir]

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.

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

New Dimension: =weekstart(PROCESS_STEP_START_DATE)

Expression for # Tickets:

Expression for Avg Days:

[sunny_talwar]

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

[cbaqir]

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.

[sunny_talwar]

Can you share what you tried?

[cbaqir]

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

[sunny_talwar]

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

WeekStart(PROCESS_STEP_START_DATE) as WeekStart_PROCESS_STEP_START_DATE

[cbaqir]

Updated

[cbaqir]

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

[sunny_talwar]

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

[cbaqir]

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