community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

dynamic select - month CURRENT&PAST

Meteor

Could anybody please tell me why only July month was selected? I meant to select current and Previous, Today is Aug 12, so I shall have Aug and July month but I got "2019-July" only...so anything wrong with the formula? Thanks!

 

 

 

Capture.JPG

Alteryx Certified Partner
Alteryx Certified Partner

Hi @annzhu 

 

How's your "August" Field like? Could you please take a screenshot?


Cheers,

Meteor

oh..I figured it out myself

Meteor

Thanks. I figured it out myself..

 

[Name]= ("2019-"+(DateTimeFormat(DateTimeAdd((DateTimeToday()),-1,'month'),'%b'))) or
[Name]= ("2019-"+(DateTimeFormat(DateTimeAdd((DateTimeToday()),-2,'month'),'%b')))

Alteryx Certified Partner
Alteryx Certified Partner

@annzhu 


This should work:

 

[Name] in (
DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "month"), "%Y-%b"), DateTimeFormat(DateTimeToday(), "%Y-%b"))

 

cheers.1.PNG

Meteor

Thanks for answering my question and now I have another one

Once I have the two columns selected, how do I make the comparison.

I could not just pick up "2019-Aug" and "2019-Jul" and do the comparison, because next month I will have to change it to  "2019-Sept" and "2019-Aug"...how to do it dynamically?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
A possibility is to transpose your month columns and using multi-row formula to make the comparison.

I'll show you an example, hang on.

Cheers,
Meteor

Thanks! how about Muti-field Formula. I have now two Fields: "2019-Jul" and "2019-AUG"...

Pulsar

Hi @annzhu 

Multi-Field formula tool lets you change multiple fields at the same time. It doesn't calculate based on 2 fields.

 

Your best bet, to calculate the difference, would be to transpose and summarize, as @Thableaus mentioned. That way, you can work dynamically, no matter the months. In a formula tool, you'd have to keep changing things.

Here's a workflow that does that. You may need to change the ultimate sort order, and which value gets the negative, to get your particular logic in.

 

 

image.png

Basically, convert the month heading into a date, and then sort on that. Turn the later date into a negative number. Use the summarize with Sum to find the difference.

 

Let me know if this helps.

 

Cheers,

Esther

Meteor

Many thanks! @Esther . How about if I have 300+ rows in each column, and number changes each month? 

How to update below formular then?

Capture.JPG

Labels