Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fgillen1
New Contributor III

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

1 Solution

Accepted Solutions
mgomezlor
New Contributor III

Re: COUNT TOTAL (DISTINCT)

May be like this

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

13 Replies

Re: COUNT TOTAL (DISTINCT)

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

fgillen1
New Contributor III

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

sasiparupudi1
Honored Contributor III

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)

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

fgillen1
New Contributor III

Re: COUNT TOTAL (DISTINCT)

Hi sasiparupudi1

That doesn't work unfortunately.

fgillen1
New Contributor III

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

qlik.JPG

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?

mgomezlor
New Contributor III

Re: COUNT TOTAL (DISTINCT)

May be like this

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

fgillen1
New Contributor III

Re: COUNT TOTAL (DISTINCT)

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

Community Browser