Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
LP27
Creator II
Creator II

Set Analysis for date fields

Hi Everyone,

  Below scenario is the scenario of set analysis  - 

Here is the Table A -

EMPStatusImplementation dateCreated dateDays Count (implementation date - Created date)Snapshot year month
1958/25/20207/5/202051Aug-20
2906/9/20205/6/202034Sep-20
3958/1/20203/5/2020149Aug-20
4958/6/20207/4/202033Aug-20
5958/12/20208/6/20206Aug-20

 

Now i need to create a Pivot table as such -

 Snapshot year month
Aug-20
Snapshot year month
Sep-20
Avg_close_time59.7534

 

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!

Thanks in advance!

Labels (2)
1 Solution

Accepted Solutions
Delestia
Contributor II
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]:
LOAD * Inline [
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]:
LOAD [Snapshot year month]
	,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]:
LOAD [Snapshot year month]
	,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];

 

 

View solution in original post

2 Replies
Delestia
Contributor II
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]:
LOAD * Inline [
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]:
LOAD [Snapshot year month]
	,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]:
LOAD [Snapshot year month]
	,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];

 

 

LP27
Creator II
Creator II
Author

Perfect! This Worked. 

Thanks 🙂