Building expenses and output model

[bellesol]

Hi Community,

I need to build a report that shows information (as per the user`s selection).

I have 2 tables, one contains: Sub-department name, expense type and expense amount in USD.

the second contains sub-department name and output for that sub-department.

I need to somehow define a Department (it is not contained in the table) and reference to it its sub-departments, 

In the report, the user will select the department (by selecting text object) and will be shown all of its expenses (the expenses of all of its sub-departments summed by expenses name.
 

Example:

Sub-department1 has expenses on salaries 10USD & Sub-department2 has expenses on salaries 20USD  the Department`s expenses on salaries is 30USD. (as shown below)

Then we will calculate the total output of our department (output of all sub-departments) and divide the expense by the total output thus getting the cost per output unit.

Example: 

source tables look like this:

expenses :

Sub Department	Expense Type	Expenses$
Production-A	Salaries	100
Production-A	Maintenance	399
Production-B	Energy	488
Production-C	Others	456
Production-Management	Salaries	456
Production-D	Depreciation	289
Development-A	Salaries	100
Development-A	Maintenance	2454
Development-B	Energy	450
Development-C	Others	600
Development-Management	Depreciation	700
Marketing-A	Salaries	800
Marketing-Management	Others	900
Marketing-B	Energy	1000
Finances-A	Salaries	1300
	Others	150

Input:

Division	Output
Production-A	300
Production-B	400
Production-C	500
Production-D	600
Development-A	700
Development-B	500
Development-C	567
Marketing-A	120
Marketing-B	468
Finances-A	109

If the user will select the production department the final table should be like this:

Department (will be displayed according to the user selection)	expences	expenses$	expenses per output
Production	Maintenance	399	0.222
Production	Energy	488	0.271
Production	Others	456	0.253
Production	Salaries	556	0.309
Production	Depreciation	289	0.161

[jwjackso]

If your "Sub Department" names are as you displayed, you can use a pre-load statement to create a department field.  I used an inline load as an example:

expenses:
LOAD *,
mid([Sub Department],1,index([Sub Department],'-')-1) as Department;
LOAD * Inline [
"Sub Department","Expense Type","Expenses$"
"Production-A",Salaries,100
"Production-A",Maintenance,399
"Production-B",Energy,488
"Production-C",Others,456
"Production-Management",Salaries,456
"Production-D",Depreciation,289
"Development-A",Salaries,100
"Development-A",Maintenance,2454
"Development-B",Energy,450
"Development-C",Others,600
"Development-Management",Others,700
];

[bellesol]

Thanks for the reply!

In the real model, the name of the sub-departments is different from each other, meaning you can't use a function to create the department name.
for example, the department 'Production' is made up of sub-department 'Red' and 'Blue'.

Somehow, I need to combine that two sub-department so when a user selects the department 'Production' it will be displayed the totals of its two sub-department

Also, the calculation should be 'type of expense total' (ex. salaries from all sub-departments) divided by the output of that department (output of all subdepartments summed) so we know that for each 1 output the expenses on salaries is X.

ex: Department- 'Production'

      The sum of expenses on 'salaries' - 556$

      The output of all sub-departments 'Production' is - 1700$ 

     The expenses for salaries is - 556/1700 = 0.327 

     *My goal is to build it in the UI.

I have attached a sample QVW.

[jwjackso]

If you want to do the mapping in the UI, look at the ValueList function .  If there a lot of sub-departments, I would use an inline table

Department:

load * from Inline [

Department,"Sub-Department"

Production, Red

Production,Blue

];