Min(Date) Occurence and Rolling 8 Weeks

[cbaqir]

I have two expressions I need to modify to only use the first instance of the DEFECT_ID entering the GOV_PROCESS_QUEUE = 'ERC - Routing' and also to only show the previous rolling 8 weeks.

I was trying to use this but it's not working: ,ROUTING_WEEK_START = {"$(='>=' & Date(WeekStart(min({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>MONTH_1), -8), 'MM-DD-YY') & '<' & Date(WeekStart(min({<GOV_PROCESS_QUEUE = {'ERC - Routing'}}>}MONTH_1), 0), 'MM-DD-YY'))"}

Avg Days expression:

aggr(avg({$<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}GOV_PROCESS_QUEUE_TAT), DEFECT_ID)

Ticket Count expression:

count({$<
GOV_PROCESS_QUEUE = {'ERC - Routing'}
  >}
DISTINCT DEFECT_ID)

Suggestions?

Thanks in Advance!

[cbaqir]

stalwar1‌

[Anil_Babu_Samineni]

I have not seen anything related your measures? Not sure, You uploaded latest version or not?

[cbaqir]

Not sure what the issue is. It is the right file.

[Anil_Babu_Samineni]

I am getting this?

[cbaqir]

You were right. I had the tab conditionally hidden. The right file is there now.

[Anil_Babu_Samineni]

Here we go, Finally in single shot.

count({$<GOV_PROCESS_QUEUE = {'ERC - Routing'},

ROUTING_WEEK_START={">=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), -8), 'MM/DD/YY'))<=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), 0), 'MM/DD/YY'))"}>}DISTINCT DEFECT_ID)

aggr(avg({$<GOV_PROCESS_QUEUE = {'ERC - Routing'},

ROUTING_WEEK_START={">=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), -8), 'MM/DD/YY'))<=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), 0), 'MM/DD/YY'))"}>}GOV_PROCESS_QUEUE_TAT), DEFECT_ID)

[cbaqir]

I think this is close but not exactly what I need. Because a DEFECT_ID can go through this step multiple times, the rolling 8 weeks could show a count for the most current week but the avg days from months ago (like below). How do I always just show the last 8 weeks if the avg days could be associated with the first week and the count is associated with later weeks?

[Anil_Babu_Samineni · Accepted solution]

You can set irrespective selection for that like

aggr(avg({1<GOV_PROCESS_QUEUE = {'ERC - Routing'},

ROUTING_WEEK_START={">=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), -8), 'MM/DD/YY'))<=$(=Date(WeekStart(max({<GOV_PROCESS_QUEUE = {'ERC - Routing'}>}MONTH_1), 0), 'MM/DD/YY'))"}>}GOV_PROCESS_QUEUE_TAT), DEFECT_ID)

[cbaqir]

thanks