topic Re: Distinct count Per Group of Dimensions in QlikView

Distinct count Per Group of Dimensions

Tue, 19 Jul 2011 14:46:37 GMT

Hi Guys,

Seems to be simple but I am not getting this one.

I have a chart with 3 Dimensions and 1 Expression

The Three dimensions are

1) Processchain

2) ProcesschainText

3) Client

and the expression is Count(distinct [Composite role/User_UNAME])

The chart Looks as below and the Field [Composite role/User_UNAME] is as given below.

Now my problem is Here if I select a User->[Composite role/User_UNAME ] as below it shows 1 for all the Processchains.

Actually I need the count as 1 as the Processchains are almost same. For Ex here.. 91170_01 to 91170_08 should be considered as 91170 and count should be for the above user only 1 for all these Process chains and not individually 1 per each processchain as above in diagram.

Any help?

Thanks
Sravan

Distinct count Per Group of Dimensions

swuehl — Tue, 19 Jul 2011 15:26:14 GMT

Hi Sravan,

how do you decide that process chains are almost the same? If you decide that using the process chain number, first part, disregarding the part after "_", you could maybe build a new process chain ID from that just to count the "really" distinct process chains.

Regards,

Stefan

Re: Distinct count Per Group of Dimensions

Wed, 20 Jul 2011 13:35:37 GMT

Hi Stefan,

Thanks for your answer.

I decided that the Processchains are same based on the text of Processchain. See the previous attached screenshot.

I did the splitting of the Field using Subfield in the following way

subfield(Processchain,'-',1) as Processchain1

subfield(Processchain,'-',2) as Processchain2

Now I need to use this Processchain1 to Remove the users which are redundant for 91170

for Example For Processchain1= 91170, the User QMN_SUB is present in everyrow. It must be only once if the processchain1 is same( as in the attached case).

Can this be done in chart or it must be done in Script?

Regards
Sravan

Re: Distinct count Per Group of Dimensions

swuehl — Wed, 20 Jul 2011 13:56:31 GMT

Hi Sravan,

so you want to display only one row? I think you got this result if you remove Processchain and Processchain_2 from dimensions and do a count(distinct ProcessChain_1) as expression.

If you want to show also Processchain and Processchain_2 that might be a bit more tricky.

Could you describe how the table should look like then, i.e. you want the expression to be shown only once. In which row then?

It might be possible to check the dimension level an calculate the expression conditional or as subtotal.

Just some ideas.

Stefan

Re: Distinct count Per Group of Dimensions

swuehl — Wed, 20 Jul 2011 14:00:56 GMT

Another idea,

if the second part of the processchain always follow the scheme _01, _02, _03 and always starts with _01,

then you might do something like

count(if(processchain2='01', whateveryoucount, 0)

you can force QlikView to show also 0 values in presentation tab of properties, I think.

But not sure what you are after.

Stefan

Re: Distinct count Per Group of Dimensions

Wed, 20 Jul 2011 14:39:03 GMT

Hi Stefan,

Thanks for answer.

swuehl schrieb:
Hi Sravan,
so you want to display only one row? I think you got this result if you remove Processchain and Processchain_2 from dimensions and do a count(distinct ProcessChain_1) as expression.

It is not about row. what I want is, if the Processchain is same, For example 91170 I get a count of user QMN_SUB as 8 (see the fig below)

This User must be counted only once and not 8 times as in my chart now. Thats my problem. I hope I am clear now.

Regards

Sravan

Re: Distinct count Per Group of Dimensions

swuehl — Wed, 20 Jul 2011 14:48:59 GMT

Hi Sravan,

sorry, I can't see a count of 8 anywhere in the fig.

I see the rows with a count of 1 each, because the expression is evaluated per row.

So what is the expression that gives you 8?

For example, if you put

=count(distinct Processchain1)

in a textbox and select the user, you should get

as result, shouldn't you?

Sorry if I am a bit slow today.

Stefan

Distinct count Per Group of Dimensions

swuehl — Wed, 20 Jul 2011 14:57:59 GMT

As I said in a previous post,

if you don't want to count the subchains, remove them from the dimension list (Keeping only Processchain1 and text).

Is there any need to show these details for you? Then you might consider splitting the information (count of processchain table and detailed listing of all subchains.

Or use subtotals.

Regards,

Stefan

Re: Distinct count Per Group of Dimensions

Fri, 22 Jul 2011 10:18:39 GMT

Hi Stefan,

I solved this by changing the data in the Sourcesystem. Thanks for your help. I think I could not express my problem correctly.

Thanks and Regards

Sravan