delta calculation between two rows

[martav]

Hello! I'm new here and on report studio too.I have this situation:
(relational package)
                     month 1       2      3 ....   12
item 1 year A  value A1  value A2  ....
           year B  value B1  value B1 .....
delta row here:(value A1- value B1)...
item 2 year A  value A1  value A2  ....
           year B  value B1  value B1 .....

I'm not able to insert a row with the difference between the value of the two years for each month.
The two years are chosen from the user with two prompts.

thanks for the help!

[gpollock]

I was able to accomplish something similar, but I put everything on one row.  Here's the gist of what I did:

Create your query to be Year, item, Value1.  Create a detail filter so year is either A or B.  For testing, add a filter to only show one item (you'll remove this filter later).
Add Data Item "ValueA1" with the following rule.
Case when year=yearA then 1 else 0 end.  Be mindful of your aggregate and rollup aggregate function.  I set both of mine to total.

Create a new data item called Delta1.  Set its expression to ValueA1-ValueB1.  Set both aggregates to Calculated (for subtraction you could use total, but calculated is a good practice if you ever add multiplication/division).

View tabular data for your query.  You should see two rows, one for each year.  The row for year A will have 0 for its ValueB1 and a positive delta.  The row for yearB will have 0 for its ValueA1 and will have a negative delta.

In your list, drag over item, ValueA1, ValueB1, and Delta1.  Do not bring year into your list.  The auto group and sum will show everything on one line, and will calculate the delta.  If you're seeing issues, go back and verify your aggregate functions of your query data items.

Once everything looks good, add the rest of the values, and remove the item filter.

[R Ambre]

Hi ,

Does your data looks like attached structure in your table.
If yes then I have solution for this.

[nsaxena]

Hi Rasika,

I have similar issue and data is aligned with what you showed in screenshot..

Can you kindly share what you did to get delta with year/month as prompt

Thanks!

[R Ambre]

Hi ,

May be you would find Solution tedious but It works.

Please refer attached document for solution

Let me know if you stuck anywhere.