topic Re: Distinct Count in QlikView

Distinct Count

jayanttibhe — Mon, 22 May 2017 16:39:22 GMT

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

Re: Distinct Count

Sergey_Shuklin — Mon, 22 May 2017 16:46:05 GMT

Hello, Jayant!

Like this?

Re: Distinct Count

Sergey_Shuklin — Mon, 22 May 2017 16:47:51 GMT

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

Re: Distinct Count

sunny_talwar — Mon, 22 May 2017 16:50:19 GMT

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?

Re: Distinct Count

jayanttibhe — Mon, 22 May 2017 17:26:39 GMT

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

Re: Distinct Count

jayanttibhe — Mon, 22 May 2017 17:27:32 GMT

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

Re: Distinct Count

Sergey_Shuklin — Mon, 22 May 2017 17:53:32 GMT

May be this the point?

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

Re: Distinct Count

jayanttibhe — Mon, 22 May 2017 17:54:21 GMT

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