Set Analysis for date fields

[LP27]

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 month Aug-20	Snapshot year month Sep-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!

Thanks in advance!

[Delestia · Accepted solution]

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]

Perfect! This Worked. 

Thanks 🙂