Announcements
cancel
Showing results for
Did you mean:
Creator II

## Set Analysis for date fields

Hi Everyone,

Below scenario is the scenario of set analysis  -

Here is the Table A -

 EMP Status Implementation date Created date Days Count (implementation date - Created date) Snapshot year month 1 95 8/25/2020 7/5/2020 51 Aug-20 2 90 6/9/2020 5/6/2020 34 Sep-20 3 95 8/1/2020 3/5/2020 149 Aug-20 4 95 8/6/2020 7/4/2020 33 Aug-20 5 95 8/12/2020 8/6/2020 6 Aug-20

Now i need to create a Pivot table as such -

 Snapshot year monthAug-20 Snapshot year monthSep-20 Avg_close_time 59.75 34

Logic for Avg Close_time = Avg({<Status={'95'}>} [Implementation Date] - [Created])

Now In the above set analysis defined, I need to include the implementation date  that falls under the same snapshot year month field only.

Example -For  Snapshot year month Aug-20 column in pivot table, It should select only Status=95 and implementation date  is within Aug 2020 and Avg for  [Implementation Date] - [Created].

Note: I have tried this script and did not work  - Avg({<Status={'95'}, Implementation Date=>} [Implementation Date] - [Created])

Kindly let me know if this is not clear or have any question.

Any tips or guidance will be appreciated!

Labels (5)

• ### Set Analysis

1 Solution

Accepted Solutions
Contributor II

Instead of a single complicated set analysis why not instead use a proxy table to accomplish the same aggregation for you? For instance the two options below will make a calculated table for you in which you can make a pivot table of [Status] over [Snapshot year month] measured by Sum[Avg_close_time]

[Set Analysis]:
EMP,	Status,	Implementation date,	Created date,	Days Count (implementation date - Created date),	Snapshot year month,
1,	95,	8/25/2020,	7/5/2020,	51,	Aug-20,
2,	90,	6/9/2020,	5/6/2020,	34,	Sep-20,
3,	95,	8/1/2020,	3/5/2020,	149,	Aug-20,
4,	95,	8/6/2020,	7/4/2020,	33,	Aug-20,
5,	95,	8/12/2020,	8/6/2020,	6,	Aug-20];

[Pivot_Option1]:
,AVG([Days Count (implementation date - Created date)]) AS [Avg_close_time]
RESIDENT [Set Analysis]
WHERE [Status] = 95 AND MonthStart([Implementation date]) = MonthStart(DATE(DATE#([Snapshot year month],'MMM-YYYY')))
Group By [Snapshot year month];

[Pivot_Option2]:
,AVG([Implementation date]-[Created date]) AS [Avg_close_time]
RESIDENT [Set Analysis]
WHERE [Status] = 95 AND MonthStart([Implementation date]) = MonthStart(DATE(DATE#([Snapshot year month],'MMM-YYYY')))
Group By [Snapshot year month];

2 Replies
Contributor II

Instead of a single complicated set analysis why not instead use a proxy table to accomplish the same aggregation for you? For instance the two options below will make a calculated table for you in which you can make a pivot table of [Status] over [Snapshot year month] measured by Sum[Avg_close_time]

[Set Analysis]:
EMP,	Status,	Implementation date,	Created date,	Days Count (implementation date - Created date),	Snapshot year month,
1,	95,	8/25/2020,	7/5/2020,	51,	Aug-20,
2,	90,	6/9/2020,	5/6/2020,	34,	Sep-20,
3,	95,	8/1/2020,	3/5/2020,	149,	Aug-20,
4,	95,	8/6/2020,	7/4/2020,	33,	Aug-20,
5,	95,	8/12/2020,	8/6/2020,	6,	Aug-20];

[Pivot_Option1]:
,AVG([Days Count (implementation date - Created date)]) AS [Avg_close_time]
RESIDENT [Set Analysis]
WHERE [Status] = 95 AND MonthStart([Implementation date]) = MonthStart(DATE(DATE#([Snapshot year month],'MMM-YYYY')))
Group By [Snapshot year month];

[Pivot_Option2]:
,AVG([Implementation date]-[Created date]) AS [Avg_close_time]
RESIDENT [Set Analysis]
WHERE [Status] = 95 AND MonthStart([Implementation date]) = MonthStart(DATE(DATE#([Snapshot year month],'MMM-YYYY')))
Group By [Snapshot year month];

Creator II
Author

Perfect! This Worked.

Thanks 🙂