on 05-14-2013 5:09 PM
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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
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
Hi,
Right click on Crosstab table >> go to Format Table>> select "Show Right Footer" and insert the diference formula.
Thanks,
Jothi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.