How to combine two time period & create a new field for new period

[nevilledhamsiri]

As per the attached data, it has reported Month, Paid Month but I need to create a new field as Tran_Month only when Reported Year & Month equal to Paid Year & Month in the script. After that I need to pick Month provision+ Under over adjustment for that particular created Tran_Month. How this is done.

Thanks In advance

Neville

[breno_morais]

Can you attach simple read?

[vishsaggi]

Cannot see sample data attached.

[nevilledhamsiri]

Hi, Please find the attachments

[breno_morais]

Good Morning Neville!

I use IF converting in Text for interpretation

Look this:

In 'IT IS' or 'NOT IS' put what you need

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'IT IS','NOT IS') as TRAN_MONTH

Filter:

[nevilledhamsiri]

Hi Breno, Thank you very much for your response, Could you please see my requirement & see how your solution could be worked out

I need to get the claim charge which is the addition of Monthly provision & Under or over adjustment

But these two fields need to be considered only when it is "IT IS" as you rightly.

But for the data I have once this is done, only Months provision is picked but under or over adjustment are not picked. Why it is? .

Also if I am to pick the claim charge for different Month I think I need to write a set expression. Hope for that too you will help me out.

Expect you may help me on this I think with your help I am almost near to my out put

[nevilledhamsiri]

Please see the qvd I tried but wont get the output

[breno_morais]

Can I suppose the Month have 30 days? Or Can I show remaining value in Days?

[breno_morais]

Neville,

I have attached an application for you see, it has two new columns. The first is interval between two dates in days and Second is interval between two dates with the value minus 30.

First expression:

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'NOT OVER'

,fabs(Interval(REPORTED_DATE-PAID_DATE,'d')))

Second Expression:

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'NOT OVER'

,IF(fabs(Interval(REPORTED_DATE-PAID_DATE,'d'))- 30 > 0,fabs(Interval(REPORTED_DATE-PAID_DATE,'d'))-30, 0))

Is this the correct answer? If it's mark as CORRECT, it would help me a lot.

[nevilledhamsiri]

ok. Say if the charge is 15000 as of  May as an example, cannot it be taken as the charge of May?