Min/Max Function

[#1]

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.

[tresesco]

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.

[Gysbert_Wassenaar · Accepted solution]

You can use the rangemax and rangemin functions here:

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