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)
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)
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?