Distinct Count

[jayanttibhe]

Hi,

In the attached example I want to show a chart based on DISTINCT Count of Profiles.

My requirement is along with Country, I want Status as well however, the status should be  MAX(Value) per Profile.

i.e. When nothing selected  for country  it should show -  'Open'  (for value = 20)

when Country  = US  - it should show -  'Closed'  (for value = 15)

when Country  = Canada  - it should show -  'Open'  (for value = 20).

In any case it should not interfere with the ProfileCount.  Profile is at lower Granularity than Country

Note : 1 Profile is May be shared across the countries --- So when I use Country Dim --  Distinct Count should be 1 .... it should not count twice.

Please have a look in.

Thanks

[Sergey_Shuklin]

Hello, Jayant!

Like this?

[Sergey_Shuklin]

I forgot to name 'Open'/'Close' but it's can be easily solved in calculated dimension.

[sunny_talwar]

When nothing is selected you want Open? for both Canada and US? also why would you see 1 for distinct profile when one country is selected?

[jayanttibhe]

Thanks Sergey - I wanted corresponding status  as dimension as well for Max value ...  Idea is Status is at lower Granular level than Profile... Here, I dont want Distinct Count of Profile = 2 because of the multiple status per profile.....

[jayanttibhe]

Yes Sunny - 

as responded to Sergey - I wanted corresponding status  as second dimension as well for Max value per Profile / per country ...  Idea is Status is at lower Granular level than Profile... Here, I dont want Distinct Count of Profile = 2 because of the multiple status per profile.....

[Sergey_Shuklin]

May be this the point?

I let myself to create a variable, hope you don't mind!

[jayanttibhe]

Hi Sunny,

Please find attached - I would like to explain again.  Basically this requirement is weird and may not make much sense.

But we basically want to show the Count on the chart for Distinct Profile  = Total Distinct Profile. Which often fails as we have Status which is at lower granularity. If you see in the attachment,

For Country Selected  = Canada  we have following data,

Country	Profile	Status	Value
Canada	1	Closed	2
Canada	2	Closed	10
Canada	2	Open	20

Now Count of Distinct Prof = 2

But due to status it shows  1 for Open and 2 for Closed  ; hence total as 3 however it's not matching the overall DISTINCT Count of profile which is 2 .

Hence, I want to show only those STATUS Where  Value Per Profile is MAX .... i.e. in this case it should show status as  Open for Profile 2 as we have value  = 20