Trying to get a difference out of two values in two tables

[shehul]

I have an invoice and credit note table. Need to find the net amount(ie invoice amount - credit note amount.
Also have a fan trap on the invoice table hence have made an alias and two contexts. One connecting invoice and credit note and other connecting invoice and invoice2.
Variance doesnt work (On selecting invoice amount and credit note amount, the icon remains disabled)
Hence I created a measure 'net amount' ((ie invoice amount - credit note amount). Tried using both the invoice table as well as the alias.
But the data in the 'net amount' field comes as junk when I select invoice no, invoice amount, credit note amount and net amount. Any way I can solve this.
Please let me know if any more details are needed

 

[Subhashab]

the basic aim of you resolving the fan trap is that the two measures should not be calculated in the same SQL. so how can you make a measure object in the universe. this will surely give you wrong result( undo ur fan trap solution)

i would suggest you to first check in BO whether it si generated the split SQL first. and them make a object inBO to calculate the net amount.
you may aslo have to use multi cube depending on the relation between the split sqls (join , sychronized)

 

[shehul]

Thanks, Subhashab...
BO has generated split SQL for the net amount. Also, my measures are not being calculated in the same SQL. I have also made an object to calculate the net, but just as i said it gives me wierd results.
Could you elaborate on the multi cube.. the sql's are syncronized..
Any other ideas

 

[Subhashab]

Ok the moment the split sqls are synchronized it means that the relation between the results of the two SQL is one to many. meaning one invoice line has many credit note line. in that case you have to use multi cube on the credit note measure.

see help of multicube and let me know if you have any more queries

 

[Sridharan]

SQL Synchronization happens when one of the Dimension is not compatible with the other Split SQL that BO has generated. You need to find out whether thats how the objects are. Otherwise it should do a join and everything should be ok. As Subhash told you could try MultiCube to the Rescue in your present case.

Sri

 

[shehul]

Thanks Guys...
I think I had wrongly picked up a dimension field from the aliased table. Should have picked from the main invoice table. Maybe thats what caused the problem

 

[Subhashab]

only the measure object should be dervied from the alias table rest of the objects should be derived from the main table only.