Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
fgillen1
Contributor III
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
Contributor III
Contributor III

May be like this

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

View solution in original post

13 Replies
sunny_talwar

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

fgillen1
Contributor III
Contributor III
Author

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
Master III
Master III

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

fgillen1
Contributor III
Contributor III
Author

Hi sasiparupudi1

That doesn't work unfortunately.

fgillen1
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III

May be like this

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

View solution in original post

fgillen1
Contributor III
Contributor III
Author

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