topic Re: Dashboard slowdown when i using the Aggr function with sum function in QlikView

Dashboard slowdown when i using the Aggr function with sum function

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

Hi community,

I want calculate the cost per mile and cost per weight and cost per pallet. I achieved the my result but dashboard slowdown.

This is the scenario.

Shipment	Miles	Amount	Pallets	Weight	Service	CustomerName	CarrierName	Mode	ChargeGroup
7457778	2681	359.11	26	11623	LH Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7457778	2681	50	26	11623	Stop Off	Rockwell Automation	Rockwell International	Truckload	Accessorial
7457778	2681	85.01	26	11623	LTL Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7457778	2681	1875.34	26	11623	Consol Linehaul	Rockwell Automation	Rockwell International	Truckload	Transportation
7457778	2681	739.22	26	11623	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7593211	2681	38.06	11	4778	LTL Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7593211	2681	192.17	11	4778	LH Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7593211	2681	346	11	4778	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7593211	2681	50	11	4778	Stop Off	Rockwell Automation	Rockwell International	Truckload	Accessorial
7593211	2681	1061.77	11	4778	Consol Linehaul	Rockwell Automation	Rockwell International	Truckload	Transportation

Here i want same shipment has different rates with different services but same mile, weight and Pallets.

So i need sun up the Amount of distinct shipment, miles, Weight and Pallets.

My Expressions like this:

Cost per miles --> Sum ({<Mode = {'TruckLoad'}> }Amount)/ sum({<Mode = {'TruckLoad'}> }aggr(Miles, Shipment))

Cost per Weight --> Sum (Amount)/Sum(aggr(Weight, Shipment))

Cost per Pallet --> sum(Amount)/sum(aggr(Pallets, Shipment))

Here i have different mode are there. so applied mode condition.

Please anybody suggest

Thanks,

ASHOK

jagan‌adamdavi3s‌awhitfield‌hic‌vinieme12‌

Re: Dashboard slowdown when i using the Aggr function with sum function

Thu, 13 Apr 2017 10:01:43 GMT

Did u Tried like this ?

Sum (Amount)/(aggr(sum(Weight), Shipment))

check that is it giving correct result .let me know if I am wrong

Re: Dashboard slowdown when i using the Aggr function with sum function

ashokpaladugula — Thu, 13 Apr 2017 10:10:46 GMT

Hi Manoj,

thanks for quick reply. but it is not working. It doesn't return any value.

Thanks

ASHOK

Re: Dashboard slowdown when i using the Aggr function with sum function

Kushal_Chawda — Thu, 13 Apr 2017 10:23:08 GMT

Please provide sample data with expected output

Re: Dashboard slowdown when i using the Aggr function with sum function

stabben23 — Thu, 13 Apr 2017 10:41:48 GMT

If its possible, try to map/join Weight, Pallets, Miles into Your transactiontable where you have Amount.

I.ll think shipment will be the Keyfield to use if you for ex use mapping load and applymap.

If they are in the same table there is no need to use aggr.

Re: Dashboard slowdown when i using the Aggr function with sum function

ashokpaladugula — Thu, 13 Apr 2017 10:50:48 GMT

Hi kushal,

Expected output is like this.

For shipment 7457778

Cost per miles --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 2681 = 1.1595225

Cost per Weight --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 11623 = 0.26745934

Cost per Pallet --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 26 = 119.564615

Thanks,

ASHOK

Re: Dashboard slowdown when i using the Aggr function with sum function

ahaahaaha — Thu, 13 Apr 2017 10:56:37 GMT

Hi ASHOK,

May be as in attached file?

Regards,

Andrey

Re: Dashboard slowdown when i using the Aggr function with sum function

stabben23 — Thu, 13 Apr 2017 11:06:03 GMT

try if this could be an alternative:

Sum ({<Mode = {'TruckLoad'}> }Amount)/ only({<Mode = {'TruckLoad'}> }Miles)

Re: Dashboard slowdown when i using the Aggr function with sum function

ashokpaladugula — Thu, 13 Apr 2017 11:41:13 GMT

Hi Andrey,

Thanks for your reply.

As per your attachment, we are able show the shipment wise cost per mile. but suppose if we select a particular customer, then sum up the all individual shipment's miles. i.e here we have 2 shipments. so miles should be 2681+2681 = 5362. if we take distinct miles it goes wrong.

Thanks,

ASHOK

Re: Dashboard slowdown when i using the Aggr function with sum function

ahaahaaha — Thu, 13 Apr 2017 12:04:02 GMT

If to analyze the content of your data, maybe the value "Transportation" in field "ChargeGroup" is a sign of miles? Then Sum(DISTINCT Miles) is replaced by expression Sum({<ChargeGroup={'Transportation'}>} Miles)?

New version in the attached file.

Re: Dashboard slowdown when i using the Aggr function with sum function

Kushal_Chawda — Thu, 13 Apr 2017 12:26:27 GMT

Cost per miles --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 2681 = 1.1595225

Sum ({<Mode = {'TruckLoad'}> }Amount)/Sum ( TOTAL <Shipment>{<Mode = {'TruckLoad'}> } distinct Miles)

Cost per Weight --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 11623 = 0.26745934

Sum (Amount)/Sum ( TOTAL <Shipment> distinct Weight )

Cost per Pallet --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 26 = 119.564615

Sum (Amount)/Sum ( TOTAL <Shipment> distinct Pallet )

Re: Dashboard slowdown when i using the Aggr function with sum function

ashokpaladugula — Fri, 14 Apr 2017 06:59:25 GMT

We cannot apply distinct on miles, weight or pallets. Because different may have same number of miles then it cannot come sum up the value. so result become wrong.

Re: Dashboard slowdown when i using the Aggr function with sum function

Kushal_Chawda — Sat, 15 Apr 2017 05:42:32 GMT

Please share one example of the same.

Re: Dashboard slowdown when i using the Aggr function with sum function

ashokpaladugula — Sat, 15 Apr 2017 06:13:15 GMT

Shipment	Miles	Amount	Pallets	Weight	Service	CustomerName	CarrierName	Mode	ChargeGroup
7457778	2681	359.11	26	11623	LH Fuel Surcharge	Rockwell Automation	ProTrans International	Truckload	Fuel
7457778	2681	50	26	11623	Stop Off	Rockwell Automation	ProTrans International	Truckload	Accessorial
7457778	2681	85.01	26	11623	LTL Fuel Surcharge	Rockwell Automation	ProTrans International	Truckload	Fuel
7457778	2681	1875.34	26	11623	Consol Linehaul	Rockwell Automation	ProTrans International	Truckload	Transportation
7457778	2681	739.22	26	11623	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7501508	4030	18.01	12	2641	LTL Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7501508	4030	527.58	12	2641	Consol Linehaul	Rockwell Automation	Rockwell International	Truckload	Transportation
7501508	4030	101.03	12	2641	LH Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7501508	4030	50	12	2641	Stop Off	Rockwell Automation	Rockwell International	Truckload	Accessorial
7501508	4030	163.74	12	2641	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7531529	2998	16.11	12	1567	LTL Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7531529	2998	140.05	12	1567	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7531529	2998	107.08	12	1567	LH Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7531529	2998	50	12	1567	Stop Off	Rockwell Automation	Rockwell International	Truckload	Accessorial
7531529	2998	559.2	12	1567	Consol Linehaul	Rockwell Automation	Rockwell International	Truckload	Transportation
7593211	2681	38.06	11	4778	LTL Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7593211	2681	192.17	11	4778	LH Fuel Surcharge	Rockwell Automation	Rockwell International	Truckload	Fuel
7593211	2681	346	11	4778	Consol Pickup	Rockwell Automation	Rockwell International	Truckload	Transportation
7593211	2681	50	11	4778	Stop Off	Rockwell Automation	Rockwell International	Truckload	Accessorial
7593211	2681	1061.77	11	4778	Consol Linehaul	Rockwell Automation	Rockwell International	Truckload	Transportation

Here suppose if we take distinct of miles or weight or pallets you will get following

miles - 2681, 4030 and 2998

Pallets - 26,12 and 11

Weight - 11623,2641 and 4778, right.

Then

if we calculate the Cost per mile for all shipments as follows

CPM = Sum(Amount)/ Sum(Miles) (i.e sum up amount of all rows / sum up individual shipments miles )

= (359.11+50+85.01+1875.34+739.22+18.01+527.58+101.03+50+163.74+16.11+140.05+107.08+50+559.2+38.06+192.17+346+50+1061.77)/(2681+4030 +2998 + 2681) = 0.526995

= 6529.48/12390 = 0.526995

but as per you query we get following result

CPM = Sum(Amount)/ Sum(Miles)

(359.11+50+85.01+1875.34+739.22+18.01+527.58+101.03+50+163.74+16.11+140.05+107.08+50+559.2+38.06+192.17+346+50+1061.77)/(2681+4030 +2998) => 0.526995

= 6529.48/9709 => 0.672518 but this wrong right?

hope you will understand what i'm expecting.

anyway thanks once again.

Thanks

ASHOK

Re: Dashboard slowdown when i using the Aggr function with sum function

swuehl — Sat, 15 Apr 2017 12:12:31 GMT

Have you considered creating a dimensional table for Shipments with its attributes like Miles, Pallets, Weight, one record per shipment, and linking this table to your cost fact table by Shipment ID?

This should return correct aggregates without the need for advanced aggregation.