Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data Processing

ankitgupta
7 - Meteor

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

 

201806201807201809201810201811201812201901201902201903201904
34243020401025185060

 

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

 

201806201807201809201810201811201812201901201902201903201904
343434343433.3233.233.233.233.2
 242424242423.5223.5223.5223.52
  30303030303029.429.4
   20202020202019.6
    404040404040
     1010101010
      25252525

 

Thanks,

Ankit

6 REPLIES 6
jamielaird
14 - Magnetar

Hi @ankitgupta 

 

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

Screenshot 2019-05-19 at 09.20.39.png

 

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
7 - 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

 

 

danilang
19 - Altair
19 - Altair

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
19 - Altair
19 - Altair

Hi @ankitgupta 

 

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

 

Dan

ankitgupta
7 - Meteor

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

 

 

cchow_
5 - Atom

This was helpful! Thanks!

Labels