topic Re: Set Analysis for date fields in App Development

Set Analysis for date fields

LP27 — Sat, 16 Nov 2024 17:58:30 GMT

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!

Re: Set Analysis for date fields

Delestia — Wed, 14 Oct 2020 02:09:54 GMT

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];

Re: Set Analysis for date fields

LP27 — Wed, 14 Oct 2020 06:31:37 GMT

Perfect! This Worked.

Thanks 🙂