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 · Accepted solution]

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!

[ankitgupta · Accepted solution]

@jamielaird , thanks for replying. In the output what I need is that, after every 6 months the number should set to 98% of the original # , In your workflow you sent, the # reduced to 98% from 7 month, but once we reached in month 13 it showing null, what should we do to show...

Month 1 to 6 - Original # say A 

Month 7 to 12 - 98% of Original say B 

Month 13 to 18 - 98% of B

.

.

.

Month N to N - 98 % of 

Also the script should check if the Months going beyond current month, then it should drop the future months columns. 

Thanks,

Ankit

[danilang · Accepted solution]

Hi @ankitgupta 

To only show the values from the current month and before, add a filter just before the Crosstab tool with the following expression

[Month] < DateTimeFirstOfMonth()

To mitigate the 13th month = null problem, change the Condition expression in the Multi-Row tool  to 

DateTimeDiff([Month],[Start Month],"months")<=100

Dan

[danilang]

Hi @ankitgupta 

You really should mark @jamielaird's answer as a solution since he answered your original post.

Dan

[ankitgupta]

Thank you @jamielaird @danilang for helping me on this, appreciate it.

[cchow_]

This was helpful! Thanks!