Using Iterno() to generate 1 value per month instead of per day

[greend21]

I was looking at the information at the below link for finding the number of effective policies at a date, but instead of knowing as of any date I am looking for month end. Basically, since Iterno creates a value for each policy on each day there is too much data. I would rather be able to look at the data from a month end standpoint. Is there  away to change Date( FromDate + IterNo() – 1 ) as ReferenceDate for a monthly view of the data? IterNo would need to create one record per policy per month end date which would greatly reduce the generated table.

Creating Reference Dates for Intervals

Edit:

‌

hic Henric, would you happen to know if this is possible?‌

[sunny_talwar]

May be this

Date(MonthStart(FromDate, IterNo() – 1)) as ReferenceDate

[greend21]

No, this is still generating well over 100 million rows. I'm currently trying to play around with a variation of this to see if it works though.

[sunny_talwar]

How does you While statement look like? If you have not changed the While statement, then the above change will have no impact in the number of rows generated

[sunny_talwar · Accepted solution]

Should be like this

LOAD Date(MonthStart(FromDate, IterNo() – 1)) as ReferenceDate

FROM ...

While MonthStart(FromDate, IterNo() – 1) <= MonthStart(ToDate);

[greend21]

Currently it is While IterNo()<= PolExpDt-PolEffDt+1;

For some reason what you recommended is not recognized.

[sunny_talwar]

The functions name is IterNo() and not InterNo()

[greend21]

Not sure if it is exactly what I need yet but your suggestion greatly limited the rows created. Thanks!

[sunny_talwar]

I guess you can keep this thread open until you are 100% certain of a solution you needed. or you can come back and open a new thread if you find any issues.

[sunny_talwar]

I also an issue with your code... move this parenthesis to all the way at the end....