topic Re: Count multiple values based on distinct values in QlikView

Count multiple values based on distinct values

Sat, 04 Feb 2017 17:24:54 GMT

Hi Team,

I am trying to write an expression which would list down all customers who are not following the below rule.

Rule to be followed is: One Project ID should only have a single Customer.

Eg:

=aggr(if(Count(DISTINCT([Project ID]))=1,if(count(distinct([Customer Name]))>1,[ Customer Name])),[Customer Name])

I tried set analysis with aggregation function couldn't figure out how to get the above solution

Anil_Babu_Samineni — Sun, 05 Feb 2017 16:32:47 GMT

What was the logic behind on this, I am not fully understand your concern. I am assuming this?

I would highly recommend to you, Don't create 2 Threads for single issue

If(Count(DISTINCT [Project ID]) = 1 and Count(DISTINCT [Customer Name]) >1, [Customer Name])

Aggr(If(Count(DISTINCT [Project ID]) = 1 and Count(DISTINCT [Customer Name]) >1, [Customer Name]), [Customer Name],[Project ID])

swuehl — Sun, 05 Feb 2017 17:43:32 GMT

Maybe create a straight table with project id and customer name as your two dimensions and as expression

=Count(TOTAL<[Project ID]> DISTINCT [Customer Name]) = 1

Enable 'suppress zero values' on presentation tab (and you can hide the expression column if you want, too)

This should only show the combinations of projects and customers where your condition is fulfilled.

sunny_talwar — Sun, 05 Feb 2017 17:55:19 GMT

May be this in a straight table:

Dimension

Customer Name

Expression

Avg({<[Project ID] = {"=Count(DISTINCT [Customer Name]) > 1"}>}1)

or text box object like this

Concat(DISTINCT {<[Project ID] = {"=Count(DISTINCT [Customer Name]) > 1"}>} [Customer Name]), Chr(10))

MarcoWedel — Mon, 06 Feb 2017 01:08:14 GMT

or using calculated listbox:

hope this helps

regards

Marco

Mon, 06 Feb 2017 05:00:54 GMT

Thanks Marco for the file. It helped me to analyze the data a from a different view.

MarcoWedel — Tue, 07 Feb 2017 23:42:30 GMT

you're welcome

glad it helped

regards

Marco