Join the Inspire AMA with Joshua Burkhow, March 31-April 4. Ask, share, and connect with the Alteryx community!
community Alteryx IO MyAlteryx
alteryx Community
Search
This Board
  • Global Community
  • This Category
  • This Board
  • Knowledge
  • Users
 cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

Calculating Previous month Number from current month

shivanand612
7 - Meteor
2 weeks ago

Hello ,

 

I have an interesting case here. 

I have dataset which has 3 months of data and data includes Emp ID, Date, Emp Designation, State, Town, City. 

i would like to calculated the previous month # emp are there in it.

 

Note; if the emp got promoted then his designation will change also his location might change.

 

attached the data set here.

 

Thank you 

 

Data set:

DateEmp IDEMP DesignationStateTownCity
01-12-2024A1234Associate 1INDCAT1
01-12-2024B2345Associate 1HKGGKT1
01-12-2024C3456Associate 1BPJJPT1
01-12-2024D4567Associate 1DWRWRT1
01-12-2024E5678Associate 1SWRWRT1
01-12-2024F6789Associate 1UBLBLT1
01-12-2024G2583Associate 2SWEWET2
01-12-2024H3691Associate 3UBLBLT2
01-01-2025A1234Associate 1INDCAT1
01-01-2025B2345Associate 1HKGGKT1
01-01-2025C3456Associate 1BPJJPT1
01-01-2025D4567Associate 1DWRWRT1
01-01-2025E5678Associate 1SWRWRT1
01-01-2025F6789Associate 1UBLBLT1
01-01-2025G2583Associate 2SWEWET2
01-02-2025A1234Associate 1INDCAT1
01-02-2025B2345Associate 1HKGGKT1
01-02-2025C3456Associate 1BPJJPT1
01-02-2025D4567Associate 1DWRWRT1
01-02-2025E5678Associate 1SWRWRT1
01-02-2025F6789Associate 1UBLBLT1
01-02-2025G2583Associate 2SWEWET2
01-02-2025H3691Sr. AssociateINDCAT3
01-02-2025K1476Sr. AssociateSWEWET3

 

 

Result :

DateCM MonthPM Month
01-02-202597
01-01-202578
01-12-202480
Labels:
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

10 REPLIES 10
Luke_C
17 - Castor
17 - Castor
2 weeks ago

Hi @shivanand612 

 

Here's one way based on your example:

  1. Format date to be a date field
  2. Group and count by date
  3. Sort dates
  4. Use multi-row tool to get the previous month count

image.png

New Workflow2.yxmd
Reply
lwolfie
11 - Bolide
2 weeks ago

I would try something like this.

PM Sample.yxmd
Reply
0 Likes
lwolfie
11 - Bolide
2 weeks ago

@Luke_C  My coffee wasn't on board yet to remember the Multi-Row tool.  That's why Alteryx is great, because there are multiple ways to do something!

Reply
shivanand612
7 - Meteor
2 weeks ago

Thank you Luke!

 

In the large data i am not getting correct numbers  if I add the city as my group in Multirow formulae i am getting near by number.

 

is there any way to do this calculation dynamically. 

Add - In my result i want to show all the dimension which i mentioned above 

 

Thank you

Reply
shivanand612
7 - Meteor
2 weeks ago

.

Reply
0 Likes
shivanand612
7 - Meteor
2 weeks ago

Give more context to this.

 

I am looking the result like this. 

DateEmp IDEMP DesignationStateTownCityCM CountPM Count
01-02-2025A1234Associate 1INDCAT111
01-02-2025B2345Associate 1HKGGKT111
01-02-2025C3456Associate 1BPJJPT111
01-02-2025D4567Associate 1DWRWRT111
01-02-2025E5678Associate 1SWRWRT111
01-02-2025F6789Associate 1UBLBLT111
01-02-2025G2583Associate 2SWEWET211
01-02-2025H3691Sr. AssociateINDCAT310
01-02-2025K1476Sr. AssociateSWEWET310
01-01-2025A1234Associate 1INDCAT111
01-01-2025B2345Associate 1HKGGKT111
01-01-2025C3456Associate 1BPJJPT111
01-01-2025D4567Associate 1DWRWRT111
01-01-2025E5678Associate 1SWRWRT111
01-01-2025F6789Associate 1UBLBLT111
01-01-2025G2583Associate 2SWEWET211
01-12-2024A1234Associate 1INDCAT110
01-12-2024B2345Associate 1HKGGKT110
01-12-2024C3456Associate 1BPJJPT110
01-12-2024D4567Associate 1DWRWRT110
01-12-2024E5678Associate 1SWRWRT110
01-12-2024F6789Associate 1UBLBLT110
01-12-2024G2583Associate 2SWEWET210
01-12-2024H3691Associate 3UBLBLT210

 

Thank you

Reply
0 Likes
Luke_C
17 - Castor
17 - Castor
2 weeks ago

Hi @shivanand612 

 

Added a second branch to accomplish this, it takes into account the employee ID and matches your output.

 

image.png

New Workflow2.yxmd
Reply
0 Likes
shivanand612
7 - Meteor
2 weeks ago

thank you Luke! Awesome! :)

Reply
shivanand612
7 - Meteor
2 weeks ago

Luke, 

 

If you summaries at month level it wont give you the PM count

 

 

Reply
0 Likes
inspire ask me anything
inspire session catalog
Labels
Top Solution Authors
View All