Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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 ?

Thanks in advance!   

1 Solution

Accepted Solutions
MVP
MVP

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:

LOAD *,

  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:

Load id,

     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
3 Replies
MVP
MVP

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:

LOAD *,

  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:

Load id,

     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
MVP
MVP

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:

LOAD *,

  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:

Load id,

    MonthName(AddMonths( created_date, IterNo() -1 ) )as YearMonth

Resident INPUT

While MonthStart(created_date, IterNo() -1 ) <= Monthstart(end_date);

Not applicable

Re: Set analysis and rangesum

Thanks a lot. This is perfect!

Clip1.png

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).