Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

End/Start of Period volumes

Hi,

I have a raw file showing policy numbers, their start dates and end dates. Someting like this:

Policy Number

StartDate

(dd/mm/yyyy)

EndDate (dd/mm/yyyy)
P101/01/201030/06/2010
P201/01/201031/12/2010
P301/01/201031/12/2010
P401/02/201031/12/2010
P501/06/201030/06/2010
P601/06/201030/06/2010
P701/06/201031/07/2010
P801/06/201031/12/2010
P901/06/201031/12/2010
P1001/06/201031/12/2010

What i want to get is a table that will calculate the volumes of in-force policies at month start for the 12 months of 2010. Something like this:

MonthJan-10Feb-10Mar-10Apr-10May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10Dec-10
In-Force policies3444410777777

How to script this - any suggestions are most welcome?

I only managed to get this view but i find it hard to present it the way shown above:

MonthStartedEnded
Jan 201030
Feb 201010
Jun 201063
Jul 201001
Dec 201006

Best Regards & Thanks,

Artur

1 Solution

Accepted Solutions
Highlighted

Re: End/Start of Period volumes

So if you use interval match in the load script, you can create a Date field to use as part of your dimension:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data:

LOAD * INLINE [

    Policy Number, StartDate (dd/mm/yyyy), EndDate (dd/mm/yyyy)

    P1, 01/01/2010, 30/06/2010

    P2, 01/01/2010, 31/12/2010

    P3, 01/01/2010, 31/12/2010

    P4, 01/02/2010, 31/12/2010

    P5, 01/06/2010, 30/06/2010

    P6, 01/06/2010, 30/06/2010

    P7, 01/06/2010, 31/07/2010

    P8, 01/06/2010, 31/12/2010

    P9, 01/06/2010, 31/12/2010

    P10, 01/06/2010, 31/12/2010

];

MinAndMax:

LOAD min([StartDate (dd/mm/yyyy)]) as MinDate,

     max([EndDate (dd/mm/yyyy)]) as MaxDate

RESIDENT Data;

let MinDate = num(PEEK('MinDate',0,'MinAndMax'));

let MaxDate = num(PEEK('MaxDate',0,'MinAndMax'));

DROP TABLE MinAndMax;

Calendar:

Load Date($(MinDate)-1+iterno()) as Date, Date(monthstart($(MinDate)-1+iterno()),'MMM YYYY') as Month autogenerate 1 While iterno() <= $(MaxDate)+1 - $(MinDate) ;

let MinDate = null();

let MaxDate = null();

IntervalMatch(Date)

LOAD [StartDate (dd/mm/yyyy)], [EndDate (dd/mm/yyyy)] RESIDENT Data;

Then you can create a pivot table where

Dimension: Month

Expression: count(distinct [Policy Number])

And then pivot the dimension so it goes across the top as columns.

Example file is also attached.

View solution in original post

12 Replies
Highlighted
Not applicable

Re: End/Start of Period volumes

Hey Artur,

Is it possible to attach a sample file.

How do you determine which is a In force policy and how do you calculate the months(based on what field/logic)

Thanks

AJ

Highlighted

Re: End/Start of Period volumes

So if you use interval match in the load script, you can create a Date field to use as part of your dimension:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data:

LOAD * INLINE [

    Policy Number, StartDate (dd/mm/yyyy), EndDate (dd/mm/yyyy)

    P1, 01/01/2010, 30/06/2010

    P2, 01/01/2010, 31/12/2010

    P3, 01/01/2010, 31/12/2010

    P4, 01/02/2010, 31/12/2010

    P5, 01/06/2010, 30/06/2010

    P6, 01/06/2010, 30/06/2010

    P7, 01/06/2010, 31/07/2010

    P8, 01/06/2010, 31/12/2010

    P9, 01/06/2010, 31/12/2010

    P10, 01/06/2010, 31/12/2010

];

MinAndMax:

LOAD min([StartDate (dd/mm/yyyy)]) as MinDate,

     max([EndDate (dd/mm/yyyy)]) as MaxDate

RESIDENT Data;

let MinDate = num(PEEK('MinDate',0,'MinAndMax'));

let MaxDate = num(PEEK('MaxDate',0,'MinAndMax'));

DROP TABLE MinAndMax;

Calendar:

Load Date($(MinDate)-1+iterno()) as Date, Date(monthstart($(MinDate)-1+iterno()),'MMM YYYY') as Month autogenerate 1 While iterno() <= $(MaxDate)+1 - $(MinDate) ;

let MinDate = null();

let MaxDate = null();

IntervalMatch(Date)

LOAD [StartDate (dd/mm/yyyy)], [EndDate (dd/mm/yyyy)] RESIDENT Data;

Then you can create a pivot table where

Dimension: Month

Expression: count(distinct [Policy Number])

And then pivot the dimension so it goes across the top as columns.

Example file is also attached.

View solution in original post

Highlighted
Contributor III
Contributor III

Re: End/Start of Period volumes

Hi Nicole,


Many thanks for your help - this works perfectly for me now.

Best Regards,

Artur

Highlighted

Re: End/Start of Period volumes

Please mark correct and helpful answers so that others can find solutions to their problems too

Highlighted
Contributor III
Contributor III

Re: Re: End/Start of Period volumes

Hi Nicole, thanks for your help so far!

in my example i also want to report on New policies and expired/cancelled policied (by End date). What's happening after introducing IntevalMatch is that the synthetic key is causing loops in the data model and the calculation is wrong (my script is probably too basic hence the loop).

My workaround was to stop using Intervalmatch and calculate monthly values of New Policies and Expired/Cancelled Policies. Then in the pivot table i used Above() function to calculate accumulated values of New Policies and Expired/Cancelled Policies. My In-Force volumes is just a simple equasion: New Policies - Expired/Cancelled Policies. The pivot consists of the following :

Dimensions:

CalendatMonthAndYear

Expressions:

Started - =count({<DateType={'Start'}>} distinct Policy)

Expired/Cancelled =count({<DateType={'End'}>} Policy)

Live =rangesum(Above(Live),Started)

Dropped =rangesum(Above(Dropped),[Expired/Cancelled])

InForce =Live-Dropped

(the full code is in the attached app, please)

But this solution has some disadvantages: pivoting needs to be switched off as when you change the layout of the table the results go wrong, there are 2 extra columns that i need to hide, In-Force policies volumes are calculated at month end whereas IntervalMatch was giving month-start volumes.

Would you have any clues on how to override the loop and the synthetic key in the data model with IntervalMatch?

I have attached 2 versions of my app:

1. InForcePoliciesByMonth - IntervalMatch causes loops.qvw

2. InForcePoliciesByMonth - IntervalMatch replaced by Above.qvw

rgds,

Artur

Highlighted

Re: End/Start of Period volumes

I'm not sure why you have the PoliciesFinal table.  It shouldn't be needed as the data is already in the Data and Master_Calendar tables...

For the interval match table, interval match on CalendarDate instead of on CalendarMonthAndYear.

Then your data structure should look something like this (the link between InForce and Master_Calendar should not be a dotted line any longer once you get rid of the loop by doing what I mentioned above):

Highlighted
Contributor III
Contributor III

Re: End/Start of Period volumes


Hello Ajay,

i have attached one, please.

rgds

Artur

Highlighted
Contributor III
Contributor III

Re: Re: End/Start of Period volumes

Nicole, many thanks again.

without the PoliciesFinal table I do not know how to calculate volumes of New Policies (based on Start date) and Expired/Cancelled Policies (Based on EndDate) in the same pivot that shows the In-Force ones. I need to get something like this:

MonthNew PoliciesExpired PoliciesIn-Force Policies at month start
Jan 2010303
Feb 2010104
Mar 2010004
Jun 20106310

rgds,

Artur

Highlighted

Re: End/Start of Period volumes

Can you post the file with that fix?  I can help you get that table then.