Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Split the billing by months based on the frequency level in pivot table

Hi Team,

I am working on the requirement, requirement is to plot on which month a particular policy@ is due for billing and to identify the billing Frequency of a particular policy and plot on which month the billing will fall. You will base the starting billing month on the Date value (consider month only).

Available values for Frequency are 1, 2, 4, 12

1 = only 1 billing for the whole year

2 = bi yearly billing (every 6 months)

4 = quarterly billing

12 = every month billing

Attached the source data and the required output.

Note: Logic should be implement in script level,

This is the critical issue. Can any one help me on this. Thanks.

1 Solution

Accepted Solutions
pavansonu99
Creator
Creator

Source:

LOAD * INLINE [

Policy No, Date, Frequency

101, 4/3/2015, 12

102, 6/5/2015, 1

103, 2/3/2015, 4

104, 7/5/2015, 4

105, 4/4/2015, 2

106, 7/7/2015, 2

];

Finel:

load *,

Date(AddMonths(MonthStart(Date),(IterNo()-1)*(12/Frequency)),'MMM') as Mon

Resident Source

while IterNo()<=Frequency;

table:

load [Policy No], Date, Frequency,

date(SetDateYear(Mon,15),'MMM') as Month

Resident Finel;

Drop Table Source,Finel;

View solution in original post

4 Replies
pavansonu99
Creator
Creator

Try this

this will help you

Source:
LOAD * INLINE [
Policy No, Date, Frequency
101, 4/3/2015, 12
101, 6/5/2015, 1
101, 2/3/2015, 4
101, 7/5/2015, 4
101, 4/4/2015, 2
101, 7/7/2015, 2
];


Finel:
load *,
AutoNumberHash128([Policy No]&'-'&Date&'-'&Frequency) as %Key,
Date(AddMonths(MonthStart(Date),(IterNo()-1)*(12/Frequency) ),'MMM') as Month


Resident Source
while IterNo()<=Frequency;
;

Drop Table Source

Anonymous
Not applicable
Author

Hi Pavan,

Thanks for the reply, when i have multiple policy numbers the billing frequency for months getting null and billing value (1) , So months are repeating to show the billing frequency. Only Feb & April values coming has the issue. the values has to come to arrow placed position,since the 'null' is presenting in that same place for feb & April displaying two times to represent the value '1'. Please find the issue screen below

Pivot Output.png

pavansonu99
Creator
Creator

Source:

LOAD * INLINE [

Policy No, Date, Frequency

101, 4/3/2015, 12

102, 6/5/2015, 1

103, 2/3/2015, 4

104, 7/5/2015, 4

105, 4/4/2015, 2

106, 7/7/2015, 2

];

Finel:

load *,

Date(AddMonths(MonthStart(Date),(IterNo()-1)*(12/Frequency)),'MMM') as Mon

Resident Source

while IterNo()<=Frequency;

table:

load [Policy No], Date, Frequency,

date(SetDateYear(Mon,15),'MMM') as Month

Resident Finel;

Drop Table Source,Finel;

Anonymous
Not applicable
Author

Thanks a lot Pavan.