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]

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

[Anonymous]

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

[sasiparupudi1]

May be

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

[sunny_talwar]

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))

[Anonymous]

Hi sasiparupudi1‌

That doesn't work unfortunately.

[Anonymous]

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

[sunny_talwar]

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?

[mgomezlor · Accepted solution]

May be like this

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

[Anonymous]

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