Need a Dimension Value as SetAnalysis Filter

[MacGyver]

Hi @ all...

I try to get delivery costs from our table, based on a order. So first of all, i try to describe our Datamodel.

Table "ShippingCosts" with Sample Data

%carrier	deliver_to	cost_per_pack
DHL	DE	2,00
DHL	NL	4,00
DHL	ES	9,00
...	...	...
UPS	DE	3,50
UPS	NL	6,90
UPS	ES	12,90
...	...	...

Next Table should be our Maintable:

Table "Orders" with Sample Data

%ordernumber	delivered_country
123456	DE
123457	NL
123458	ES
...	...
123500	DE
123501	NL
...	...

There is one more Table:

Table "Tracking" with Sample Data

%ordernumber	tracking_no	%carrier
123456	456788	DHL
123456	456789	DHL
123456	456799	DHL
123457	456712	DHL
123458	456735	DHL
123500	456820	DHL
123456	1z0815123	UPS
123456	1z0815139	UPS
123501	1z0815124	UPS

Attention: Tracking count for 123456 = 3 items by DHL and 2 items by UPS ! (split delivery).

I tried to generate a Diagram Table to report the shipping cost by each Ordernumber.

My Dimension of corse: %ordernumber

My calculation shold match with a result like this:
sum({<[%carrier]={'%carrier'}, [deliverd_to]={'delivered_country'}>} shipping_cost) **

In this sample to ordernumber 12346 it would be:
(3x DHL to DE) + (2x UPS to DE) = (3x 2,00) + (2x 3,50) = 13,00 EUR.

** But you already could see - inserting a Dimension, does not work for this calculation.

I have got no Idea how to get this multidimension array into this formular.

Got anyone have a clou?

Regards,

Mirco

[marcus_sommer · Accepted solution]

I think that's not a use-case for a set analysis which is quite the same as a selection. Therefore I could imagine that approaches like:

count(tracking_no) * avg(cost_per_pack)

and possibly wrapped with an aggr() like:

sum(aggr(count(tracking_no) * avg(cost_per_pack), %ordernumber, %carrier))

might be more suitable.

Important will be in each case how the tables are associated which means that the relevant data-combinations are really available.

Personally I doubt that I would try to calculate it in this way and also separating these information into at least 3 tables else I would map the shipping-costs to each tracking_no - and afterwards a simple sum() would be enough.

[vinieme12]

This would be easier if you can create a composite key in your data model to map cost based on Carrier and DeliverTo

[MacGyver]

Thanks for replying my question. @Marcus1 has the closest idea to inspire my thoughts on this task.

It's not realy working, what i was to get with this solution, but we recognised that we are on an absolute wrong way.

In our case, we need to calculate a sum of shippingcosts. This is only in the moment of wrinting this order to our database, the right way. One time a year - we get new shipping rates. So in fact - after some years, we need the archieved shipping coast - not the current.

So we decide to resolve this in our ERP System. Our order-database will get a new fiel "payed_shippingcosts" on an other step in our workflow. At least - i just get read this field for my analytics.

Thanks for your suggestions.