topic Pro-rata days in QlikView

Pro-rata days

Thu, 22 Jul 2010 12:34:28 GMT

Hello,

could you please help me calculating a prorata No. of days ?

My formula works fine to find the total No.of days. However, if I want to calculate the prorata of days limiting the end date, up until the 31/12/2010 I cant get it right...

thanks for your help

The formulas I used:

(num(interval(date([End Contract],'DD/MM/YYYY') - date([Start Contract],'DD/MM/YYYY')),'####')) as Totaldays => OK

(num(interval(date([End Contract]='31/12/2010','DD/MM/YYYY') - date([Start Contract],'DD/MM/YYYY')),'####')) as Prorata => NOT OK

Thanks

Pro-rata days

Thu, 22 Jul 2010 12:58:17 GMT

I think you should have it written this way, without "[End Contract] =" :

(num(interval(date('31/12/2010','DD/MM/YYYY') - date([Start Contract],'DD/MM/YYYY')),'####')) as Prorata => NOT OK

Pro-rata days

Fri, 23 Jul 2010 09:32:25 GMT

Thank you for your answer Miha. You were right, it worked when I changed to your suggestion.

However, I realised I mistook and changed my formula to find the correct No. of days for the contracts ending in 2010. I paste my formula just in case it can be useful to other people. Thanks again.

(num((date(IF([End Contract]<=yearend(makedate(year(today()))),[End Contract],YearEnd(makedate(year(today())))),'DD/MM/YYYY')),'####')) as min_end_date_contract