Total Expression

[khaycock]

Hello,

This might be simple but I'm not quite sure how to figure it out...

In a day, a dealer may have more than one order combined into a single delivery (in this case, there are 5 orders in one delivery on 9th Jan). There will only be one freight charge for the delivery but the equation i have calculates a charge for each order number as it is counting the amount of drops. This is the equation i have:

NUM(if($(vDrops)<=75, $(vDrops)*19.64, if($(vDrops)<=100, $(vDrops)*19.12, if($(vDrops)<=125, $(vDrops)*18.59, if($(vDrops)<=126, $(vDrops)*18.05, 0)))),'£##,###')

(There are different charges for the amount of drops)

Also the vDrops variable is: count(KEY2)

Any ideas? Is this possible in a pivot table?

[adamdavi3s]

Hi Kathryn,

If you could share a sample of your data it would help... but could you just split the UPS charge across the orders?

So something like 

charge / aggr(count(order number) ,Dealer Name, Date)

[khaycock]

Hi Adam,

So sorry for the late response. I have attached a sample file with a quite rubbish attempt to do what you suggested. Are you able to have a look for me?

Thanks

[adamdavi3s]

Hi Kathryn,

Not a problem, much easier with the data!

I was slightly misleading with the AGGR, the TOTAL <dimension> syntax actually worked better in this case.

I think this is what you're looking for:

[UPS Freight Charges] / count(TOTAL <Name, DATE, Week> ORDER_NUMBER)

[khaycock]

I don't think I am able to hide the original UPS Freight Charges column though as I don't want those charges to be shown as well as the new expression?

[khaycock]

Sorry ignore my other response...

I've manage to get the correct amount of columns now, but once the amounts are split, when minimised, dont equal the correct charge.

The UPS charge is meant to be £19.64 per drop for each dealer and even though there are many different order numbers, the charge should still be for the overall amount of drops. It works for the first one but the others aren't showing the right total.

[adamdavi3s]

Hi Kathryn,

What selections do you have on there?

Mine appears to work OK, ah sorry just twigged it is because you have removed the UPS column... leave it with me

[khaycock]

I put the UPS column stuff into a variable and it works fine at the Order Number level, but when I minimise to Dealer or Date level the charges stay at 19.64 when it really should then be higher or to amount of drops x Charge.

Does that make sense?

[adamdavi3s]

Yep absolutely, I'll come up with a solution sorry I should have thought ahead to removing the column

[khaycock]

Hi Adam,

I've decided to just not allow pivoting in the table and leave it fully extended so it works fine. Is it possible for help on another expression on this dashboard please?

I have a simple sum expression that is meant to be summing 20.00 as 20.00 as there that is the only cost on that date, but for some reason, it is spitting off 40 and I have no idea why!

I've attached a doc and a pic too.

Thanks!