Difference from 2 totals

[#1]

Hi to all,
I have a problem I need to create a structure like the follow

A) Profit - Account 1 --- 100€
Account 2 --- 100€
Total A -- 200€

B) Loss - Account 3 --- 60€
Account 4 --- 10€
Total B -- 70€

MOL A - B 130€

C) Other - Account 5 --- 30€
Account 6 --- 20€
Total C -- 50€

Create total is easy the problem is addin one row A-B, i tried to create many tables (one for each section A, B, A-B, C...) but I don't know how can create difference from two tables.

Anybody can help me...

Many thanks
Andrea

[#2]

Hi Andrea,

I've attached a close example, maybe it could be help you.

Ask me whatever you want.

Best regards.

[#3]

I'm not aboe to interpret your requirement fully. But a littile thought.

Create a table like below:

ProffitLoss:
DIRECTORY '$(QVDGdir)';
LOAD
'PR' as Type,
.......
....... as Amount
FROM .... where ...
CONCATENATE
LOAD

'LO' as Type,
.......
....... as Amount
FROM .... where ...
CONCATENATE
LOAD

'OT' as Type,
.......
....... as Amount
FROM .... where ...

Now, in any expressions, Amount of Profit/Loss/Other can be referenced by the type and can be aggregated on any dimension hierarchy like object accoutn, BU, company etc.

--Arun

[#4]

Hi Miuguel thnk you for your suggestion but i don't have MOL row in master data should be a calculate row....

Master DATA

Group	Account	Value
Profit	AC1	€ 100,00
Profit	AC2	€ 100,00
Lost	AC3	€ 60,00
Lost	AC4	€ 10,00
Other	AC5	€ 30,00
Other	AC6	€ 20,00

should be in Qlick

Group	Account	Value
A) Profit	AC1	€ 100,00
	AC2	€ 100,00
Total Profit		€ 200,00
B) Lost	AC3	€ 60,00
	AC4	€ 10,00
Total Lost		€ 70,00
MOL		€ 130,00	The problem is on this line
C) Other	AC5	€ 30,00
	AC6	€ 20,00
TOTAL Other		€ 50,00

Thanks Andrea

[#5]

Hi Arun thank you for your suggestion but i don't have MOL row in master data should be a calculate row....

Master DATA

Group	Account	Value
Profit	AC1	€ 100,00
Profit	AC2	€ 100,00
Lost	AC3	€ 60,00
Lost	AC4	€ 10,00
Other	AC5	€ 30,00
Other	AC6	€ 20,00

Should be in Qlick

Group	Account	Value
A) Profit	AC1	€ 100,00
	AC2	€ 100,00
Total Profit		€ 200,00
B) Lost	AC3	€ 60,00
	AC4	€ 10,00
Total Lost		€ 70,00
MOL		€ 130,00	The problem is on this line
C) Other	AC5	€ 30,00
	AC6	€ 20,00
TOTAL Other		€ 50,00

Thanks Andrea

[#6]

Don't we create a resident table from the above, with MOL as derived field?

ProfitLosswithMOL:
LOAD
AccountID,
....
...
if(Type='PR',Amount,null())-if(Type='LO',Amount,null()) as MOL,
....
resident ProfitLoss;

if your intention is still different, let us wait for some expert opinions to come in...

--Arun

[#7]

Hi Andrea,

I can see you don't understand my example, I've used two inlines tables because I don't have your data, but the first one must be your table data but the second one is really a template inline table, I mean, you have to do a template including the accounts and the calculations that you need, so, it will be relationated trough the account field, see my example again.

Best regards.