3 Replies Latest reply: Nov 29, 2015 11:10 AM by Aravind Sasidharan

# Set analysis and rangesum

Hi,

I am trying to use set analysis and range sum to compute the total number of records that were active at a some point in the month

For example, if these are the records:

id state created_date modified_date

1 expired 01/12/2014 10/12/2014

2 expired 01/12/2014 10/03/2015

3 active 01/12/2014 01/12/2014

4 expired 10/01/2015 12/01/2015

5 expired 10/01/2015 11/03/2015

6 active 10/02/2015 10/02/2015

7 expired 10/03/2015 11/03/2015

The expected o/p is

Dec-14 3

Jan-15 4

Feb-15 4

Mar-15 5

E.g: For Jan-2015 the result should be 4 -

1 active record from Dec 2014 + 2 created in Jan + 1 from Dec which actually expired in March 2015

The modified_date is updated when a record is expired

I tried this:

rangesum(above(Count({\$<[state] = {'active'} >} id), 0,12))

+ Count({1<[state] ={'expired'}, modified_date.Calendar.Month ={">=\$(created_date.Calendar.Month)"}>} id)

I couldn't figure out how to do the second count statement. How can I aggregate the records which are currently not active but were active at some point during the month.Is it possible to achieve this without using separate calendar varables ?

• ###### Re: Set analysis and rangesum

You can do it like HIC demonstrated in

Creating Reference Dates for Intervals

Then you just need to add a master calendar:

The Master Calendar

```INPUT:
if(state = 'active', today(), modified_date) as end_date
INLINE [
id, state, created_date, modified_date
1, expired, 01/12/2014, 10/12/2014
2, expired, 01/12/2014, 10/03/2015
3, active, 01/12/2014 ,01/12/2014
4, expired, 10/01/2015, 12/01/2015
5, expired, 10/01/2015, 11/03/2015
6, active, 10/02/2015, 10/02/2015
7, expired, 10/03/2015, 11/03/2015
];

id_x_Dates:
Date( created_date + IterNo() -1 ) as ReferenceDate
Resident INPUT
While IterNo() <= end_date - created_date + 1 ;

Load Date,Date as ReferenceDate, Month(Date) as Month, Monthname(Date) as YearMonth ;
Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;
Load Min(ReferenceDate)-1 as MinDate, Max(ReferenceDate) as MaxDate resident id_x_Dates;
```

Then create a straight table with dimension YearMonth and expression

=Count(Distinct id)

YearMonth Count(Distinct id)
7
Dez 20143
Jan 20154
Feb 20154
Mrz 20155
Apr 20152
Mai 20152
Jun 20152
Jul 20152
Aug 20152
Sep 20152
Okt 20152
Nov 20152
• ###### Re: Set analysis and rangesum

If you don't need the reference dates on a daily basis (e.g. you don't want to drill down to week or dates), you can even simplify the script to

```INPUT:
if(state = 'active', Date(today()), modified_date) as end_date
INLINE [
id, state, created_date, modified_date
1, expired, 01/12/2014, 10/12/2014
2, expired, 01/12/2014, 10/03/2015
3, active, 01/12/2014 ,01/12/2014
4, expired, 10/01/2015, 12/01/2015
5, expired, 10/01/2015, 11/03/2015
6, active, 10/02/2015, 10/02/2015
7, expired, 10/03/2015, 11/03/2015
];

id_x_Dates:
MonthName(AddMonths( created_date, IterNo() -1 ) )as YearMonth
Resident INPUT
While MonthStart(created_date, IterNo() -1 ) <= Monthstart(end_date);
```
• ###### Re: Set analysis and rangesum

Thanks a lot. This is perfect!

Do you reckon there would be a performance hit when the number of records, average life span and date range grows?

Also wondering if this approach would work for incremental load (insert & updates).