cancel
Showing results for
Did you mean:

Alteryx designer Discussions

SOLVED

Data Processing

Meteor

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

Highlighted
Alteryx Certified Partner

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!

Meteor

@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

Magnetar

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

Magnetar

Dan

Meteor

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

Atom