EXPRESSION ISSUE

[#1]

WHy are these two giving me different results?? 1st one seems correct , but I expect both to give me the same outcome.

(Count({<NEW_24Mths_FLAG={'1'},Year={$(=Max(Year)-1)}>}  distinct [Claim No])

+

Count({<NEW_24Mths_FLAG={'1'},Year={$(=Max(Year)-2)}>}  distinct [Claim No])

+

Count({<NEW_24Mths_FLAG={'1'},Year={$(=Max(Year)-3)}>}  distinct [Claim No])) /3

//******SECOND EXPRESSION*****************************************

(Count({<NEW_24Mths_FLAG={'1'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)}>}  distinct [Claim No]) /3

[jduarte12]

Hello,

I am not totally sure, but I think the difference between the expressions is the following:

Expression 1 counts distinct [Claim No] for each one of the 3 years, sums the results and you get the average in the end;

Expression 2 counts distinct [Claim No] over the set of the 3 years, meaning that it will count distinct [Claim No] present in all the years.

Regards,

[#3]

I dont think so, cuz I used the same two expressions with a different flag and they both give me the same results

[#4]

Hi,

I think this is because u are using Distinct.

In Expression 1 : It is counting separately but

in Expression 2 : It is counting all at once, so the distinct values will be different from expression 1.

If you remove distinct from both the expressions, it should give you the same result.

Thanks,

Faredun

[Clever_Anjos]

They´re not supposed to return same value.

Suppose you have [Claim No] = 0001 into Year-1, Year -2 and Year-3,

If you use first expression, that Claim No would be counted 3 times, and if you use the second expression it would be counted only once