topic Re: Charts not displaying distinct count in QlikView

Charts not displaying distinct count

Wed, 27 Mar 2013 16:55:08 GMT

Hi,

I have 2 charts 1 for order confirmation volume and the other for 'on time' or 'overdue' order confirmations

The distinct count expression works on the first chart that just wants overall volume of confirmations (using order ID to define uniqueness)

However in the second chart with the 'on time'/'Overdue' dimension the count distinct doesn't work as it counts duplicate confirmations(sometimes we send multiple confirmations per order id).

The dimension is If(order date=confirmation date,"On time","Overdue")

can anyone help?

Re: Charts not displaying distinct count

kaushiknsolanki — Wed, 27 Mar 2013 17:26:05 GMT

Hi,

Kindly post the qvw file.

Regards,

Kaushik Solanki

Re: Charts not displaying distinct count

Wed, 27 Mar 2013 17:41:51 GMT

Hi,

I am not allowed to release the QVW outside the company, is there any advice you can offer without seeing it? do i have to reference the dimension in the expression along with the distinct identifier?

Re: Charts not displaying distinct count

Wed, 27 Mar 2013 22:24:29 GMT

So you are saying, you have a chart, with Overdue / on time as the dimension, and the expression should be a count of orders?

The expression will work would with =count(Distinct Order_ID)

Otherwise I think the issue is in the dimension, not the expression. If you have multiple confirmation dates, you are creating potentially multiple rows where one order_ID could have both a row with overdue and on time in it and this is where the double count comes from!

You will need to think about perhaps either creating a flag field in the script in the confirmation table for EG the latest confirmation date (just a 1/0) and filter the table for this "main confirmation" date.

Good practice would probably dictate that you calculate the overdue / ontime field in your script too, and stick it in the main order table. This would make a lot of things easier for you too.

Regards,

Erica

Re: Charts not displaying distinct count

Thu, 28 Mar 2013 08:27:50 GMT

Fantastic, thanks very much.