Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculation in load script using master calendar dates

Dear all,

I have need some expert advise for my problem

I have created a master calendar in my project and also I have table which has 2 date fields.

Sample 2 rows from my data:

Start DateEnd DatePremiumContractNo
01/01/201631/12/201610001
01/02/201601/02/20175002

I have a monthenddate field in my master calendar which is basically last day of the each month.

My formula is : (monthenddate - Start Date) / (End Date - Start Date) * Premium

I would like to run this formula in load script for each contract for the each month end date and my I should reach a data set which has

12 different rows for each contract after running this formula like

Policy no, Monthenddate, Function Result

1                  31/01/2016          10

1                  28/02/2016           15

1                  31/03/2016            20

.......

1                   31/12/2016          100

Your helps will be appreciated.

Many thanks,

1 Solution

Accepted Solutions
sunny_talwar

Another alternative to Kaushik's solution

Table:

LOAD *,

  ((MonthEndDate - [Start Date])/([End Date] - [Start Date])) * Premium as ResultField;

LOAD *,

  Date(Floor(MonthEnd([Start Date], IterNo()-1))) as MonthEndDate

While IterNo() <= Difference;

LOAD [Start Date],

    [End Date],

    (Year([End Date]) * 12 + Month([End Date])) - (Year([Start Date]) * 12 + Month([Start Date])) + 1 as Difference,

    Premium,

    ContractNo

FROM

[https://community.qlik.com/thread/231666]

(html, codepage is 1252, embedded labels, table is @1);


Capture.PNG

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Have a look at the attached Example.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

Another alternative to Kaushik's solution

Table:

LOAD *,

  ((MonthEndDate - [Start Date])/([End Date] - [Start Date])) * Premium as ResultField;

LOAD *,

  Date(Floor(MonthEnd([Start Date], IterNo()-1))) as MonthEndDate

While IterNo() <= Difference;

LOAD [Start Date],

    [End Date],

    (Year([End Date]) * 12 + Month([End Date])) - (Year([Start Date]) * 12 + Month([Start Date])) + 1 as Difference,

    Premium,

    ContractNo

FROM

[https://community.qlik.com/thread/231666]

(html, codepage is 1252, embedded labels, table is @1);


Capture.PNG

Anonymous
Not applicable
Author

Dear kaushik.solanki‌ would you write your script in here since I have personal edition.

Thanks,

EE

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Here is the script.

DATA:

Load *,[Start Date]&'-'&[End Date] as Key;

LOAD Date(Date#([Start Date],'DD/MM/YYYY')) as [Start Date],

  Date(Date#([End Date],'DD/MM/YYYY')) as [End Date],

     Premium,

     ContractNo

FROM

[https://community.qlik.com/thread/231666]

(html, codepage is 1252, embedded labels, table is @1);

MINMAX:

Load Min([Start Date]) as MIN,

  Max([End Date]) as MAX

Resident DATA;

  

Let vMin = num(Peek('MIN',0,'MINMAX'));

Let vMax = num(Peek('MAX',0,'MINMAX'));

Temp:

Load Floor(MonthEnd(Date($(vMin) + RowNo() -1))) as MonthEnd

AutoGenerate 1

While Date($(vMin) + RowNo() -1) < Date($(vMax));

INTERVAL:

intervalmatch(MonthEnd)

Load [Start Date],[End Date] Resident DATA;

Left join(DATA)

Load distinct [Start Date]&'-'&[End Date] as Key,MonthEnd

Resident INTERVAL;

Drop table INTERVAL;

FINAL:

Load *,(MonthEnd - [Start Date]) / ([End Date] - [Start Date]) * Premium as Value Resident DATA;

Drop table DATA, MINMAX;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!