topic Re: Min/Max Function in QlikView

Min/Max Function

Tue, 04 Jun 2013 04:22:11 GMT

I am facing issues with Min/Max function. There are two date fields Claim_Date and Paid_Date. I want to find the difference in years which I am able to do with the following:

Year(Paid_Date) - Year(Claim_Date) as Lag_years

This works in the scrip editor.

To avoid data errors I want to limit it to minimum 0 and maximum 5. In Excel or SQL this would be simply as follows:

Min(5,Max(0,Year(Paid_Date) - Year(Claim_Date)))

However, neither the minimum or maximum function are working. I have even tried the following in Expression in the pivot table:

Min(5,Max(0,Lag_Years))

Is there any special way of using Min/Max in Qlikview.

Thanks.

Re: Min/Max Function

tresB — Tue, 04 Jun 2013 05:43:29 GMT

you can try something like:

If( (Year(Paid_Date) - Year(Claim_Date))>=5, 5,

If((Year(Paid_Date) - Year(Claim_Date))<0, 0, (Year(Paid_Date) - Year(Claim_Date)))) as Lag_years

Thanks.

Re: Min/Max Function

Gysbert_Wassenaar — Tue, 04 Jun 2013 10:40:21 GMT

You can use the rangemax and rangemin functions here:

rangemin(5,rangemax(0,Year(Paid_Date) - Year(Claim_Date))) as Lag_years