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

Convert Date String to Date and Sum the value

Shank
8 - Asteroid

Hi,

 

Can any one help how to covert the date string to date and sum the value based on below condition.

 

I have data set with Date as below in excel and i need to consider data for next six month i.e., for current month it should pick values from 07.2018 to 12.2018 and in next month it should pick values from 08.2018 to 01.2019 

 

Input 

 

 

NameDateValue
A07.2018100
A08.2018200
A09.2018300
A10.2018400
A01.2019500
B08.2018600
B09.2018700
B10.2018800
C08.2018900
C09.20181000
C02.20191100

 

 

Out Put

 

NameValue
A1000
B1300
C1900
3 REPLIES 3
BenMoss
ACE Emeritus
ACE Emeritus
You can convert your date in many ways. I would use a formula in this case to create a date based on your field.

Right([field],4)+'-'+left([field],2)+'-01'

Then create a second formula to calculate the datemonth currently.

Datetimetrim(Datetimenow(),'month')

Finally create a third calculation and use the datetimedifference() function to compare your two date fields.

Then you can use a filter tool to acknowledge only those in the next 6 months.

Finally you can use a summarize tool after your filter to aggregate your data. Grouping by your name field and then summing the values.

Ben
Shank
8 - Asteroid

Thank You i am able to fix it

BenMoss
ACE Emeritus
ACE Emeritus

No problem @Shank I'm glad we could get you to a solution! The way you structured your question and including sample data made it very easy to understand the problem!

 

Ben

Labels