Data Processing

ankitgupta

Hi All, I need some help in getting a problem solved. I have a data like this:

201806	201807	201809	201810	201811	201812	201901	201902	201903	201904
34	24	30	20	40	10	25	18	50	60

Desired Output that I am looking for:

• 201806 value will go till 6 months, and from 7 month the value will be reduced to 98%, from 13th month the value will again reduced to 98% and so on. 
• Same will happen to rest of the months

201806	201807	201809	201810	201811	201812	201901	201902	201903	201904
34	34	34	34	34	33.32	33.2	33.2	33.2	33.2
	24	24	24	24	24	23.52	23.52	23.52	23.52
		30	30	30	30	30	30	29.4	29.4
			20	20	20	20	20	20	19.6
				40	40	40	40	40	40
					10	10	10	10	10
						25	25	25	25

Thanks,

Ankit

jamielaird

Hi @ankitgupta 

The attached will do the trick. I've annotated the workflows to show what is happening at each stage:

The values themselves are calculated in the multi-row formula tool, which uses the following:

IF [Month] = [Start Month] THEN [Start Value]
ELSEIF Mod(DateTimeDiff([Start Month],[Month],"months"),6)=0 THEN [Row-1:Value]*0.98 
ELSE [Row-1:Value] 
ENDIF

The Modulo function (Mod) is used to work out where the sixth months fall relative to the start date.

Hope this helps!

6th Month.yxmd