Column with text relative to 2 last columns

[qvixote]

Hello!

Here I am again with a rare need on report studio. I have a report over a PowerCube and need to achieve the following crosstable:

Code Select Expand


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  LESSER
Worker2    11    11     7     9  GREATER
Worker3    16    15    13    14  GREATER


The last column named NEW doesn't exist yet, I need to create it. It have to compare the value of the last 2 years (columns) and show a text value.

Any idea of how can I create that column?

Thanks.

[sdf]

Please see, if this is what you are looking for :

https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.1.1.doc/t_rs_insert_query_calc.html

[Lynn]

Hello!

Here I am again with a rare need on report studio. I have a report over a PowerCube and need to achieve the following crosstable:

Code Select Expand


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  LESSER
Worker2    11    11     7     9  GREATER
Worker3    16    15    13    14  GREATER


The last column named NEW doesn't exist yet, I need to create it. It have to compare the value of the last 2 years (columns) and show a text value.

Any idea of how can I create that column?

Thanks.

Here is a possible approach to pursue (I'm a little rusty on dimensional but I've done this in version 11 and it should work the same for you in 10):

Create a query calculation, nested alongside the years as you've shown, to work out the difference between the last two years:

Code Select Expand


lastChild ( [Time] ) - lag ( lastChild ( [Time] ), 1 )


This should show:

Code Select Expand


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  -2
Worker2    11    11     7     9   2
Worker3    16    15    13    14   1


Next, unlock the report layout and put a layout calculation next to the <#1234#> in your crosstab intersection. Put this expression in the layout calculation:

Code Select Expand


case
   when CellValue () < 0 then 'LESSER'
   when CellValue () > 0 then 'GREATER'
else 'SAME'
end


This will show the label on every intersection, not just in your new column. Use an advanced conditional style for the layout calculation with a condition of ColumnNumber() < 5 and set the box type property to none for this condition. Create another conditional style for the value <#1234#> with box type none for the condition ColumnNumber() = 5.

The end result should show the value in the four columns corresponding to the years 2015 through 2018 but will not show the value in your change column.

[qvixote]

Thanks for your answer, Sdf.

In the link you shared is described the general procedure to add query calculations. I know how to add query calculations, and I know thats what I need to do, but the thing is I don't know how to make the query calculation to do exactly what I've described. I have used query calculations to perform many calculations, but not anything like that. I don't know how to compare the value of only the las 2 columns (years) to indicate if the current year value is lesser or greater than the -1 year.

Thanks again for your reply.

[sdf]

i see you have a duplicate post

[MFGF]

i see you have a duplicate post

Not any more. I merged the two posts into this combined one.

MF.

[Lynn]

Thanks for your answer, Sdf.

In the link you shared is described the general procedure to add query calculations. I know how to add query calculations, and I know thats what I need to do, but the thing is I don't know how to make the query calculation to do exactly what I've described. I have used query calculations to perform many calculations, but not anything like that. I don't know how to compare the value of only the las 2 columns (years) to indicate if the current year value is lesser or greater than the -1 year.

Thanks again for your reply.

My reply was in the other thread that is now merged with this one. Hopefully this helps address your problem.

[qvixote]

Sorry, I was with connection problems and that surely have duplicated the post. Thanks, MFGF, for resolving it.

Thanks, Lynn, for your answer, it is what I was looking for, but I'm still a step from getting it solved.

First, your recomendation (lastChild ( [Time] ) - lag ( lastChild ( [Time] ), 1 )) gives me an error, but just to try I inserted directly this:

Code Select Expand

[2018] - [2017]

and get the same result (obviously, only work for 2018), but I forgot to mention an element in the table. I have previously made the following query calculation:

Code Select Expand

average ([ATENCIONES_TOTAL])

Being [ATENCIONES_TOTAL] the measure in the cross table. The previous calculation is in the rows, so I get the average of each column, like this:

Code Select Expand


         2015  2016  2017  2018   [NEW]
Worker1    15     8    12    10  -2
Worker2    11    11     7     9  2
Worker3    16    15    13    14  1
AVG        14  11,3  10,6    11  11


The NEW column only repeat the value of 2018, but I want it to show the difference between average of 2018 and average of 2017.