Pivot Table trailing turnover help

KevinjLayton

Hey,

So I'm running Oracle 11g and making an analysis with a pivot table. The rows are separated by service lines(Companies) and then the measurement is separated by columns that are a month apart. The measurement being the total count of terminated people but for now its called 'test'

See example below:

The problem I'm running into is that the total for theres should be in the low 2,000's so I have no idea where the current results are coming from. If I delete the dates column and do a count on Termination date is greater than a year from the current date I get a accurate representation but I'm trying to do a view from 12 months ago until now. So October 2017 should have a total of +2,100 and then add on to that number as the columns go up month by month until now. The formula I'm currently using is:

COUNT(CASE WHEN "Worker"."Termination Date" >= TIMESTAMPADD(SQL_TSI_YEAR, -(1),"Time"."Calendar Month Start Date")  THEN 'x' END)

What am I doing wrong here. I feel like my formula is conflicting with the dates column. Is there a function like CURRENT_DATE but pulls form the date under the column that's better than calling out the actual column?

Thomas Dodds

You want a RSUM() on your COUNT(by month)

Robert Angel

On your formula; -

SUM(CASE WHEN "Worker"."Termination Date" >= TIMESTAMPADD(SQL_TSI_YEAR, -(1),"Time"."Calendar Month Start Date")  THEN 1 else 0 END)

Is the effect that you were seeking?