How to built a difference directly in the table....?

[#1]

Hi all,

How to built a difference in the table, using expressions, avoiding the script?

Example: I have 2 independ excel - sheets with fields like "Reporting date" - 1.1.2009 and 1.2.2009 , "item "- A , "sum" - the sum for item A on 1.1.2009 "reporting date" was 30, on the 1.2.2009 it was 50.

I need to create the difference between this 50 an 30, directly in the table,just using expressions not in script! How to do that?

Many thanks for any help.

Marta

[#2]

Hi Marta,

Could you please attach a reduced version of the file, and I can work on it.

Thanks.

[#3]

One solution cna be like this:

Sheets:

LOAD ..SUMA...from Sheet1;

LEFET INNER JOIN

LOAD ..SUMB...from Sheet2;

Sum:

LOAD ...SUMA,AUMB, SUMA-SUMB as SUMDIFF resident Sheets;

.........

--Arun

[#4]

Pretty sure you can use the Above function, as long as you sort the table appropriately.

[#5]

hi,

as I said - I do not want this formula in the script, just direct in the table box. The script is very easy - I load 2 excel sheet with rows like:

Reporting date: Item Quantity

1.1.2009 A 2

1.1.2009 B 5

1.1.2009 A 10

the second excel sheet is like this one above, different is just the reporting day.

Then i added the A-item and got as a sum, in this case, 12. In the second excel sheet with other reporting day, the sum is 30. Now i need to create in the table, as a new column, the difference between this 30 and 12. Not in script...

Sorry, I can`t explain it better 🙂

Many thanks,

Marta

[#6]

Hi Marta,

You could try this in the expression box.

Thanks

[#7]

sum( Qty ) - above( sum( Qty ))

[#8]

Are the two tables joined by reporting date?

Based on you description, they should... I know you don't want to change the script, but if the two dates share the same meaning, they should stay on the same field.

Having the join in the script you could just write down the difference.

IF, and only IF, you're sure that the two tables MUST stay unrelated (double double check the "why"), than you can use an If expression, which is evil, but accomplishes the task:

If(ReportingDate1 = ReportingDate2, A-B, null)

This imply a cartesian product at runtime between the two tables, with quadratic cost. It's ugly and slow down as hell if you have many records.

In Italy we say "Advice is half salvation".

Bye!

[#9]

Regarding my last post, please note that, in any case, the two tables MUST be unrelated for the formula to work.

It they are not, you have to change the script anyway... so you shouldn't use my formula in the first place.

[#10]

Hi Xena,

no, it doesnt work as it should to 😞 it count the difference really ABOVE, independet what item should be count. So if you have

A

B

C

The difference is between A and B and not A and A.

But I think the direction is actually pretty good 🙂

thanks!

Marta