topic Re: Total Expression in QlikView

Total Expression

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Total Expression

adamdavi3s — Fri, 24 Mar 2017 12:27:15 GMT

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)

Re: Total Expression

khaycock — Thu, 27 Apr 2017 14:12:27 GMT

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

Re: Total Expression

adamdavi3s — Thu, 27 Apr 2017 17:14:56 GMT

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)

Re: Total Expression

khaycock — Wed, 03 May 2017 08:38:44 GMT

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?

Re: Total Expression

khaycock — Wed, 03 May 2017 08:57:43 GMT

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.

Re: Total Expression

adamdavi3s — Wed, 03 May 2017 15:44:34 GMT

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

Re: Total Expression

khaycock — Wed, 03 May 2017 16:00:01 GMT

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?

Re: Total Expression

adamdavi3s — Wed, 03 May 2017 17:28:57 GMT

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

Re: Total Expression

khaycock — Fri, 12 May 2017 08:33:17 GMT

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!