Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi,
Kindly post the qvw file.
Regards,
Kaushik Solanki
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?
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
Fantastic, thanks very much.