Aggr in set analysis expression incorrect

[louwriet]

Hi ,

I would really appreciate help with a complex Aggr that i use with set analysis.

I need to work out the fuel usage per delivery truck per route. At the end of the day i need to see what route is the most cost effective.

I have 3 sets of data that i concatenate to get one fact - transaction table. One is what is the value of the goods that the truck delivered. One is the expences of that truck - petrol/diesel and One is Route information - amount of kilometers per route.

Expression that is returning zero values to calculate Liter per trip :

sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum({$<data = {'Expence'}, Exp_Type = {'petrol','diesel'} >} Value )) , Month, VehicleReg, Route ))

Month , VehicleReg and Route are my dimensions. When i use this aggr just to sum the Total_Km i get the correct answer but the data expence in the expression result in 0 values.

Thank so much

Louw

[Nicole-Smith]

You're missing dimensions for one of your aggr() functions.  You should also have dimensions here:

sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum({$<data = {'Expence'}, Exp_Type = {'petrol','diesel'} >} Value ), __________) , Month, VehicleReg, Route ))

So maybe like this?

sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum({$<data = {'Expence'}, Exp_Type = {'petrol','diesel'} >} Value ), Month, VehicleReg, Route) , Month, VehicleReg, Route ))

[jerem1234]

Not sure exactly what pieces of your formula you are aggregating, but I think your parentheses are off and your missing a set of dimensions for one of the aggrs. Does this work for you?

=sum(Aggr(sum({$<data = {'TRIP'}>} Total_km), Month, VehicleReg, Route) / sum(Aggr(sum({$<data = {'TRIP'}>} Total_km) / sum({$<data = {'Expence'}, Exp_Type = {'petrol','diesel'} >} Value ) , Month, VehicleReg, Route )))

If not, can you explain what pieces of the formula you want to aggregate with what fields. Also if you can, it will be easier for you to get help if you post a sample qvw demonstrating your problem.

Hope this helps!

[louwriet]

Hi Nicole, thanks for your reply. I did try your suggestion but it did not work. I have added a demo now that will explain it better. I have also added excel sheet with sample data. Maybe my my loadscript where i concatenate datasets is causing the problem.

Thanks for the help

Regards

Louw

[Nicole-Smith]

I tried looking at the .qvw you posted, but I think you're going to need to post a small sample app (the .qvw you posted just has way too much going on).  In the app, make sure you post what you're seeing vs. what you expect to see.  This is important if you want us to help you make it right.

[louwriet]

Hi Nicole,

I have created a small demo that i hope explainde my senario that i am struggeling with better. In the model i explained what i am trying to calculate.

Maybe it is in my script where i concatenate 3 files that is causing the problem ? Maybe i must not include expences there and just exclude it out of the transaction table and build a key with vehicle regno and yearmonth to link to transaction table?

Thank so much for the help

Regards

Louw

[louwriet]

Hi Steve Dark,

I deleted your post by mistake

I got the fuel price right, i created a expense table with the fuel price in - this use vehicle reg and month as link. It seems to work but i am struggeling to get my last 2 calculations right. For the Cost per route for a vehicle i get the total cost for the month for vehicle then i divide by the number of routes. The answer displayed is correct but now i need to use this ave to take the sales per route minus this ave cost to get to the real income per route. Any idea how to get this right. Attached is the new demo model with changes in. I really appreciate the help.

[stevedark · Accepted solution]

Hi Louwrie,

If you can email me the deleted comment I can put it back in the thread - it may be helpful to someone?

Regarding your expression, if i get what you are after then you need the average for the vehicle regardless of route to take that off the Sales Total?  Whilst your sales totals are zero in your example, you would expect -656.5 and -262.6 in the Real Income column?

To do this you need to use the TOTAL statement, to ignore the dimensions when working out the average.  The only dimension you care about is the Vehicle one - so that drops in as a predicate to the TOTAL statement.  The resultant expression you are after may therefore be:

Column(1) -

(

sum(TOTAL <Vehicle_Reg_Route> Aggr( sum(TOTAL <Vehicle_Reg_Route> {$<T_Source = {'Trip'}>}Route_Km), T_Year_Month,Vehicle_Reg_Route,T_Route )) /

(sum(TOTAL <Vehicle_Reg_Route> Aggr( sum(TOTAL <Vehicle_Reg_Route> {$<T_Source = {'Trip'}>}Route_Km), T_Year_Month,Vehicle_Reg_Route,T_Route ))

/ (Aggr(sum(TOTAL <Vehicle_Reg_Route> Liters), T_Year_Month,Vehicle_Reg_Route))) * FEULPRICE / count(TOTAL <Vehicle_Reg_Route> DISTINCT T_Route)

)

If that isn't it let me know and I can take another look for you.

Cheers,
Steve

[louwriet]

Hi Steve,

Thank so much for the help. Your expression for the Cost per route worked.

Regards

Louwrie

[stevedark]

Excellent. Thanks for confirming this back to me.