Divided

bellesol — Sat, 16 Nov 2024 02:04:28 GMT

Hi,

I have two tables that connected with the field "Sub-Department"

Expenses table:

Department	Sub Department	Expense Type	Expenses$
Production	Production-A	Salaries	100
Production	Production-A	Maintenance	399
Production	Production-B	Energy	488
Production	Production-Management	Salaries	400
Development	Development-A	Salaries	100
Development	Development-A	Maintenance	2454
Development	Development-B	Energy	450

Output Table:

Sub Department	Output
Production-A	300
Production-B	400
Production-C	500
Production-D	600
Development-A	700
Development-B	500
Development-C	567

I want to display the following table:

Department	Expense Type	Expenses$	Output	Output-result
Production	Salaries	500	sum(expense on salaries)/sum(output by Sub-Department whose name begins with Production )	500/1800
Production	Maintenance	399	sum(expense on Maintenance)/sum(output by Sub-Department whose name begins with Production )	399/1800
Production	Energy	488	sum(expense on Energy)/sum(output by Sub-Department whose name begins with Production )	488/1800
Development	Salaries	100	sum(expense on salaries)/sum(output by Sub-Department whose name begins with Development )	100/1767
Development	Maintenance	2454	sum(expense on Maintenance)/sum(output by Sub-Department whose name begins with Development )	2454/1767
Development	Energy	450	sum(expense on Energy)/sum(output by Sub-Department whose name begins with Development )	450/1767

In the column "Output" I'd like to divide the sum of expenses type by department, with the total value of "output" by the sum of all output of the Sub-departments that start with the beginning of the department that displayed in the first column.

How do I create the column "output"?

Re: Divided

spividori — Thu, 31 Oct 2019 15:47:03 GMT

Hi.

See the attached example. Hope this helps!.

Regards.

Re: Divided

bellesol — Thu, 31 Oct 2019 17:15:27 GMT

Thanks for your replay!

but in my original model, I have a lot of Department and sub-department so it's not possible to build it manually in the script.

If I will add the column "Department" to the table "output" it will be possible to build the calculation in the UI?

Re: Divided

spividori — Thu, 31 Oct 2019 18:36:45 GMT

Hi.

I understand that if, add it and I try it, it would even be easier for UL.

Sorry, my english is poor. I hope to make myself understood.

Regards.

Re: Divided

bellesol — Thu, 31 Oct 2019 19:27:33 GMT

Please see the attachment

Re: Divided

spividori — Thu, 31 Oct 2019 21:09:14 GMT

See if it is what you need.

Regards.

topic Re: Divided in QlikView

Divided

Re: Divided

Re: Divided

Re: Divided

Re: Divided

Re: Divided