Hi All

I have several reports which look at transaction data going back 12 months.

A colleague has asked to look at some revenue figures going back to April 2021. This was was 21 months ago. I thought this would only involve a slight change to the formulas but something has gone awry.

The below gives me the data for this month:

DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 21*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),0,CASE WHEN {Type} = 'Assembly Build' THEN ABS({amount}) ELSE {amount} END)

However I tried this for last month:

DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 21*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),1,CASE WHEN {Type} = 'Assembly Build' THEN ABS({amount}) ELSE {amount} END)