topic help needed on agrr on count distinct in QlikView

help needed on agrr on count distinct

lmonincx — Thu, 03 Dec 2009 17:24:05 GMT

hI, i am trying to put the relative measure from a straight table into a pivot, the pivot has not the checkbox of relative and i need to do it in the expression I have 5 unique relations but one is having two campagnes, in the relative it's calcuted for that specific campagne as 1/6 is 17 % this is good, but if i do a count(distinct Relatie)/count (distinct total relatie) it's getting back with 1/5 20 % I need a formula which gives me the 17 %. Campagne,Sub,Relatie Z,Internet,123 Z,Email,456 Z,Email,678 A,Email,123 A,Internet,375 A,Internet,450 How can i solve this? see attached file with demo data Thanks helping me out. Linda

help needed on agrr on count distinct

Thu, 03 Dec 2009 17:57:16 GMT

Hi Linda,

Could you please attach the demo data, to work on.

Thanks

help needed on agrr on count distinct

lmonincx — Thu, 03 Dec 2009 18:45:35 GMT

sorry, attached the file.

thanks!

help needed on agrr on count distinct

Thu, 03 Dec 2009 22:11:45 GMT

Hi Linda,

I don't know if this is what are you looking for, I hope it could be help you.

Best regards.

help needed on agrr on count distinct

lmonincx — Fri, 04 Dec 2009 16:11:09 GMT

Many thanks the solution seems so simple

help needed on agrr on count distinct

lmonincx — Tue, 08 Dec 2009 20:34:04 GMT

Hi,

I tried the solution at my customer but if failed on the results, see attached , i expect tot see the 83,4% of kanaal isstf ( 583/699 where the 699 is the sum of all the uniek relations within the different kanaals) , leaving the expression and turning it into pivot it's using the 583/649 where 649 are the unique realtions in the campagne ( not using the 699).

I need a calculation using the 699 ( sum of unique relations over the kanaals within a campagne)

Any idea how to establish this?

Kind regards, Linda

help needed on agrr on count distinct

Wed, 09 Dec 2009 15:35:45 GMT

Hi Linda,

What about use a variable for the calculation?

See my attached file.

I hope this help you.

Best regards.

help needed on agrr on count distinct

lmonincx — Thu, 10 Dec 2009 17:22:36 GMT

Hi Miguel, I am almost there many thanks this realy is a step forward, only thing which remains is that the total line at the end is not counting up to 100% I am not able to turn off the total line, any idea if we can make it display the sum of the values ( 100%)?

Many thanks for your help!

Kind regards, Linda

help needed on agrr on count distinct

Thu, 10 Dec 2009 22:54:33 GMT

Hi Linda,

It has cost me a neuron of my brain, but, after a long time analyzing your data, I found there are some RelatieRespPos nulls and others one repeated in Kanaal aanvraag, so I've changed all again and recalculated all the expressions, check out my example attached.

By the way, I'm not using the variable any more.

I hope this finally help you.

Best regards.

help needed on agrr on count distinct

lmonincx — Fri, 11 Dec 2009 15:52:07 GMT

Hi Miguel, you are my hero, many thanks for all your time and effort, this is the solution i needed.

Have a nice weekend!

Kind regards,

Linda

help needed on agrr on count distinct

Fri, 11 Dec 2009 16:06:39 GMT

Hi Linda,

You are always well come.

Have a nice weekend too.

Miguel.