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)
And it comes up blank.
The next time any data is shown is for this formula which I thought would show 10 months ago over the 21 month period:
DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 21*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),10,CASE WHEN {Type} = 'Assembly Build' THEN ABS({amount}) ELSE {amount} END)
The above formula actually shows last months figures.
There is evidently a gap in my understanding of how this formula works.
Can anyone advise on how I would adjust this to go back 21 months? A screenshot below for wider context.
Many thanks in advance for your help.
Kind regards
Oliver