How to calculate percent for the total libne of crossTab

[magicksol13]

Hi to all,
We have the following crossTab.


                        2015 (text)                   2016 (text)                Percent% (text)
                   [PrevYearToDateSales]  [CurrYearToDateSales]
Jan                          150                               175                               16.7%
Feb                          175                               140                              -20.0%
...
Dec                          200                               180                              -10.0%
--------------------------------------------------------------------------------
TOTAL                      525                               495                              -13.3%



[CurrYearToDateSales]  and [PrevYearToDateSales] are both Data items i created in or query.
Percent is also DataItem that i create myself in ReportStudio as follows:  ([CurrYearToDateSales] - [PrevYearToDateSales]) / [PrevYearToDateSales]

QUESTION: How to make the low right corner of this crossTab actually calculate (495 - 525) / 525 to display -5.71% instead of -13.3%

I TRIED what some links are suggesting but with NO luck so far !

http://www-01.ibm.com/support/docview.wss?uid=swg21367569



Can anyone please help !

[SpareTire]

Hi!

I have tried this and failed but no harm in you trying:

Click the bottom right corner cell and in the properties, set define content to "yes". You will now have a blank cell.
Unlock the report and drag in a query caluclation. Make the query calculation, Total([CurrYearToDateSales]) - Total([PrevYearToDateSales])) / Total([PrevYearToDateSales]).

I usually continue to get a blank space on this row...

Since most of my needs come to excel dumps, my other option has been to remove the total line. Add a table below the crosstab that mimics all the fields of the crosstab but with Total formulas. (let me know if you need that explained more) looks clumsy in HTML, but works in excel.

I think if you have access to the framework manager, you can add the formula into the package itself and set a determinant to have it calculate properly at a certain level (i remember this from an FM training I took)

Hope this helps, let us know how it goes.

[magicksol13]

Thank you so much SpareTire. I am working on other priorities fr now but i will try what you suggested later on today and reply back when possible. Good day to all.

[magicksol13]

Hello again SpareTire and everyone else. I decide to give it atry bu is it normal that i get the RSV-VAL-003 Unable to find Data Item 1 in the query Query3 after performing the steps of dragging and dropping my newly created Data Item into the lower left corner of my crossTab ?
:(

[SpareTire]

Wait a sec, is this relational or dimensional? If its relational, you should have just gotten a blank space in your crosstab.

Edit: Let me clear. If this is a relational model, my first two comments are relevant (although if you get a blank I don't see how that helps you  ;D)
Here is the solution for a relational model (change the solve order of the column): http://www-01.ibm.com/support/docview.wss?uid=swg21339552

If you have a dimensional model, your admin (or you) needs to set the determinants to the level that's appropriate. You have two measures that are sales and two time dimensions (Months and Years). If the determinant can be set to the year, you will not get wonky totals because it will "determine" (pardon the pun) the aggregation granularity. Right now, i bet no determinant is set so its doing it at the lowest level which is month.

[magicksol13]

It's a relational model.