Charts not displaying distinct count

[#1]

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?

[kaushiknsolanki]

Hi,

     Kindly post the qvw file.

Regards,

Kaushik Solanki

[#3]

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?

[#4 · Accepted solution]

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

[#5]

Fantastic, thanks very much.