Use output of Count distinct to calculate metrics

[Anonymous]

I am new to QlikSense and I need help understanding how I can put this to work. I am having a hard time learning how to use this on my own without being a programmer.

I have a huge table that has a lot of orders, some of them come duplicated, tons of reasons apply as of why this happens but there's no way to actually see what’s the unique value without removing duplicates as the system itself doesn't populate any data such as main order. I don't want to remove duplicates as this forces me to get some other tools for data analysis as I am deleting important information when removing the duplicated orders.

The COUNT(distinct([order#])) formula works like a charm when it comes to count unique values, however, if I want to see an average in days of the production lead time, it counts everything (main order and duplicates) and gets me unreliable data.

I would like to know if it is possible to get the output of the distinct count to be the one getting the calculations but using the complete data set. For example:

I have a column for orders and another for hit/miss, I want to see the percentage of orders that hit the metric so the system shows Order count as 20 records, however, when I remove duplicates or else apply the distinct count formula it gets me 7 unique records.

I want to see the average lead time for the 7 unique records (or how many of them fall into hit or miss) not the 20, is this possible?

Appreciate your help.

[juraj_misina]

Hello Carlos,

are you able to calculate the Hit/Miss flag in the script? Let's say you'd create a field "Hit/Miss" which would contain respective values for all orders (even duplicates), but you'd be able to use this field in set analysis and achieve what you want:

Count(DISTINCT {<[Hit/Miss]={'Hit'}>} [order#]) //count distinct orders which hit the metric

Count(DISTINCT {<[Hit/Miss]={'Miss'}>} [order#]) //count distinct orders which missed the metric

[rawoeste · Accepted solution]

Hi,

Try to aggregate on the entity where you're having the duplicates.

The 'aggr' makes sort of a temp table which will be aggregated the way you want.

In this case, the order numbers are duplicated ..

So, in case of the count: count(aggr(ordernumber, ordernumber))

In case of the avg: avg(aggr(prodleadtime, ordernumber)).

E.g. of the result with some test data ... the 'b'-key was the duplicate in this case.