If values are repeated then desired value should reflect only at 1 place

[P_Kale]

Hi,

I am in one situation where in data policy numbers are repeated and through group by on client ID i am bringing number of policies purchased by client in original table. Since policies are repeated i am getting same numbers in front of all the policies.

Ask is :- At front end in pivote chart object want to give total distinct policy count and total policies purchased.

Below given is the data and expected summary.

DATA
CHDRNUM	PO_CLNTNUM	Policies_Issued_count	Ask is
123456	55998480	10
123456	55998480	10
234678	55998480	10	10
234678	55998480	10
678910	55998480	10
678910	55998480	10
989012	55998480	10
78656	55998480	10

Expected summary

	Look-in CHDRNUM distinct	Policies Issued
55998480	5	10

Thanks in advance

@Anil_Babu_Samineni 

@marcus_sommer 

[marcus_sommer · Accepted solution]

count(CHDRNUM) and avg([Policies Issued]) should do the job.

[deepaksahirwar]

Hi @P_Kale ,

There are different ways to achieve this, depending on your data structure and your desired output. 

PF some possible solutions:

You can use the Count function with the Distinct modifier to count the distinct values of a field or expression. For example, Count (Distinct CHDRNUM) will count the distinct policy numbers for each client ID.

You can use set analysis to specify the conditions for counting distinct values. For example, Count ({< [Status]= {'Active'} >} distinct [ID]) will count the distinct IDs that have at least one status active. 

Hope this must work fine for you

If you found our response helpful then please click on “Accept as Solution” to mark this issue as resolved. This will also help other users who might have the same question as you.

We appreciate your cooperation and feedback. 😊

Sincerely, 

Deepak 

[P_Kale]

Thanks Marcus_sommer.

It is working fine.