How to calculate distinct count value?

i encounter an issue to do calculation with distinct count value.
my user case is based on the {CM_per_truck} or {carrier_per_truck} to identify if the shipment is under Milk-run scope. then i based on the {CM_per_truck} or {carrier_per_truck} to calculate how many trucks we saved through Milk-run project.

here is the calculation field i have.
cm_per_truck= distinctCountOver({vendor_name},[{collect_truck_pro}] ,PRE_AGG)
carrier_per_truck= distinctCountOver({move_carrier},[{collect_truck_pro}], PRE_AGG)

Saved Truck Count= ifelse(
{MR_type}=‘multiple pickup’,({cm_per_truck}-1)/distinctCountOver({legal_po_id},[{collect_truck_pro}],PRE_AGG) ,
{MR_type}=‘multiple drop’, ({carrier_per_truck}-1)/distinctCountOver({legal_po_id},[{collect_truck_pro}],PRE_AGG) ,
0
)

but the saved truck calculation doesn’t seen right. especially for the numbers with decimal.

is there a better way to calculate the saved truck count?

Why are you dividing it by the distinctCount? Is this like a group?

Ultimately you are saying take the distinctCount over vendor or move carrier over the collect_truck_pro field. Then divide it by the distinctCount of legal po id over the collect truck pro.

It seems to me as this is actually getting an average instead of a difference.

It’s hard to know without understanding what all the fields mean, but can you multiply cm_per_truck or carrier_per_truck by distinctCountOver({legal_po_id},[{collect_truck_pro}],PRE_AGG)

thank you for your reply, Max.

the desire calculation logic as below, but given the PO count will cause duplication of {cm_per_truck}, how can i sum up the saved truck base on the level of {collect_truck_pro}
PS: {collect_truck_pro} will contains multiple {legal_po_id}

ifelse(

{MR_type}=‘multiple pickup’,sum({cm_per_truck}-1) of each truck,

{MR_type}=‘multiple drop’, sum({carrier_per_truck}-1) of each truck,

0

)

below is sample data of ‘multiple pickup’. can you help to advise how can I get the total with LAC calculation?

i think i find my way to calculate it.

1 Like