cancel
Showing results for 
Search instead for 
Did you mean: 

Difference between 2 columns using custom function

Former Member
0 Kudos

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,

Accepted Solutions (1)

Accepted Solutions (1)

former_member189638
Active Contributor
0 Kudos

Is it always going to be 2 years in your report..I mean are you trying to show Current year vs Previous Year report??

Then you can use Vertical Table instead of Cross Table

Former Member
0 Kudos

Hello Rakesh,

First, thank you for your response.

Is it always going to be 2 years in your report..I mean are you trying to show Current year vs Previous Year report??


Yes, it always will be 2 years. "Current year" selected by the user via prompt, and LY defined by myself using a LY predefined filter in the query panel.

Then you can use Vertical Table instead of Cross Table

And also yes, I could use it but it doesn't change my question. Does it?

I just prefer to use a cross-table because it shows the data in a more structured way.

PD: I've attached an screenshot of my report. Just left a "difference" column at the end.

Regards,

former_member189638
Active Contributor
0 Kudos

Don't use Max and Min. Instead use the below formula.

VAR1 =[VALUE] Where ([Year] = UserResponse("<Prompt Text>"))

VAR2 =[VALUE] Where ([Year] = [Predefined Filter object])

Former Member
0 Kudos

Well I achieved it using a VAriance calculation in the DB and defining it in the Universe , then dragging it in report, but if you want to do it in report, then the variance ratio needs to be done on footer of the report , (right hand side last column) , IF you only got two years in consideration.

Thanks,

Avinash

Former Member
0 Kudos

Yes, Rakesh, you got it.

I had been done like you ask, but you take the lead to reply the correct solution before.

The award of the correct solution is for you.

Thanks a lot Rakesh, Avinash and Jyothirmayee for your helpful replies.

Best Regards,

PD: I've attached a screenshoot of the resulting report. so cute! haha

Answers (1)

Answers (1)

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

Right click on Crosstab table >> go to Format Table>> select "Show Right Footer" and insert the diference formula.

Thanks,

Jothi