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

dynamic select - month CURRENT&PAST

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

13 REPLIES 13
Thableaus
17 - Castor
17 - Castor

Hi @annzhu 

 

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


Cheers,

annzhu
7 - Meteor

oh..I figured it out myself 🙂

annzhu
7 - Meteor

Thanks. I figured it out myself..

 

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

Thableaus
17 - Castor
17 - Castor

@annzhu 


This should work:

 

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

 

cheers.1.PNG

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

Thableaus
17 - Castor
17 - Castor
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,
annzhu
7 - Meteor

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

estherb47
15 - Aurora
15 - Aurora

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

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