Skip to Content
0
Former Member
May 14, 2013 at 04:09 PM

Difference between 2 columns using custom function

28 Views

Hello folks,

I have a relative easy question for the community.

I'm creating a "Container report", which shows the current stock value shipped in containers used for the customer to move his products; listing the Container value in function of the Country and the Year. I have made a cross-table with those dimensions and a ratio (container value).

| Year

_______|_____________________

Country | 2013 | 2012 |

Austria | value | value |

Bulgaria | value | value |

and so on...

I want to add a right-handed column to show the difference between the stock value shipped in containers between 2 years. For this purpose I had defined some variables:

- MaxYear as:

=max([Year])

defined as: ratio (if I try to define it as Dimension, Webi throws an exception and force it to ratio)

Output: 2013

- MinYear as:

=min([Year])

defined as: ratio

output: 2012

and finally the values according to the year:

-MaxYearValue as:

=([Value] Where ([Year] = [MaxYear]))

Defined as: Dimension

Output: If I set up a table with Country and MaxYearValue, the data showed is the sum of the 2 years. But, take the formula into account, I have done a subset of the dimension through the Where condition (=2013).

-MinYearValue

Same behavior as MaxYearValue.

- To finally define a "Difference" ratio, just the subtraction between MaxYearValue and MinYearValue

There's something I have been done wrong, but I don't know where and why.

An idea or suggestion will be welcomed.

Thank you for reading, and thanks a lot for the replys

Regards,