COUNT TOTAL (DISTINCT)

[Anonymous]

Hi All,

I am looking to get the percentage the total values within a year. I thought the formula below should work but it doesn't.

Count(DISTINCT{<MONTH_ID = {201701}>}  POLICY_ID) / Count(DISTINCT TOTAL {<MONTH_ID = {201701}>} POLICY_ID)

Anyone have any ideas?

Regards

Finbar

[sunny_talwar]

May be this

Count(DISTINCT{<MONTH_ID = {201701}>}  POLICY_ID) /

Count(DISTINCT TOTAL {<MONTH_ID = {201701}, Sales_Flag_Field_Here>} POLICY_ID)

[sasiparupudi1]

Count(DISTINCT{<sale={Y},MONTH_ID = {201701}>}  POLICY_ID) / Count(DISTINCT TOTAL {<MONTH_ID = {'201701'}>} POLICY_ID)

[stevedark]

Hi Finbar,

Have you tried both the divisor and the enumerator expressions separately?  Do these give the values that you expect?

Also, have you tried the expressions with the set analysis removed (i.e. everything inside the {}).  Again, does this give the values you expect.

The two things I can see that might be issues are, the lack of space between the DISTINCT and the {.  Also, is the MONTH_ID field a string, a number or a date?  If it is a string you should have {'201701'} and if it is a date it should be {'$(=Date(Date#('201701','YYYYMM'),'YYYYMM'))'}.

Getting data types right around date fields is critical.

Hope that helps,

Steve

[Anonymous]

Hi stevedark,

Thanks for the reply, mgomezlor had the correct expression!

Replay Finbar