topic Difference from 2 totals in QlikView

Difference from 2 totals

Fri, 16 Oct 2009 17:20:47 GMT

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

Difference from 2 totals

Fri, 16 Oct 2009 18:23:35 GMT

Hi Andrea,

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

Ask me whatever you want.

Best regards.

Difference from 2 totals

Fri, 16 Oct 2009 18:31:46 GMT

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

Difference from 2 totals

Fri, 16 Oct 2009 22:40:38 GMT

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
A) Profit	AC2	€ 100,00
Total Profit		€ 200,00
B) Lost	AC3	€ 60,00
B) Lost	AC4	€ 10,00
Total Lost		€ 70,00
MOL		€ 130,00	The problem is on this line
C) Other	AC5	€ 30,00
C) Other	AC6	€ 20,00
TOTAL Other		€ 50,00

Thanks Andrea

Difference from 2 totals

Fri, 16 Oct 2009 22:42:38 GMT

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
A) Profit	AC2	€ 100,00
Total Profit		€ 200,00
B) Lost	AC3	€ 60,00
B) Lost	AC4	€ 10,00
Total Lost		€ 70,00
MOL		€ 130,00	The problem is on this line
C) Other	AC5	€ 30,00
C) Other	AC6	€ 20,00
TOTAL Other		€ 50,00

Thanks Andrea

Difference from 2 totals

Fri, 16 Oct 2009 23:04:37 GMT

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

Difference from 2 totals

Mon, 19 Oct 2009 21:20:43 GMT

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.