13 Replies Latest reply: Mar 26, 2018 5:27 AM by Finbar Gillen

# COUNT TOTAL (DISTINCT)

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

• ###### Re: COUNT TOTAL (DISTINCT)

Within a year? but you still have the same set analysis for the denominator restricting your denominator to 201701

• ###### Re: COUNT TOTAL (DISTINCT)

Hi Sunny,

Sorry should have been more clearer. I also have a filter selected within the filter pane.

I want the KPI widget to select all policy_id's in 201701 where the filter is applied DIVIDED BY all policy_id's in 201701 where there it is not taking into consideration the filter.

Regards

Finbar

• ###### Re: COUNT TOTAL (DISTINCT)

I am not sure I understand... are you looking to get the row level sum to add up to 100%? What is your chart dimension where you performing this calculation? Try like this

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

Sum(TOTAL Aggr(Count(DISTINCT {<MONTH_ID = {201701}>} POLICY_ID), Dimension/s))

• ###### Re: COUNT TOTAL (DISTINCT)

Hi stalwar1, that doesnt work unfortunately it brings it up to 100%. I am looking for the number of policies that have had a sale 201701 as a % of the total policies on 201701

• ###### Re: COUNT TOTAL (DISTINCT)

How do you determine sale? I mean I understand total policies... but how does it different from policies which have sale and how do we know what has sale?

• ###### Re: COUNT TOTAL (DISTINCT)

I have a column in my table where I have flagged a policy with a sale as 'Y'

• ###### Re: COUNT TOTAL (DISTINCT)

May be this

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

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

• ###### Re: COUNT TOTAL (DISTINCT)

May be

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

• ###### Re: COUNT TOTAL (DISTINCT)

That doesn't work unfortunately.

• ###### Re: COUNT TOTAL (DISTINCT)

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

• ###### Re: COUNT TOTAL (DISTINCT)

May be like this

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

• ###### Re: COUNT TOTAL (DISTINCT)

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