We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

General Discussions

Discuss any topics that are not product-specific here.
SOLVED

Auto picking of Closing balance of last month as opening balance of current month

SanaUlBari
6 - Meteoroid

Hi All

 

I am trying to create a monthly recon where i want alteryx to pick opening balance of the current month from closing balance of last month (as can be seen below).

Which tool i can use to make this possible? 

 

SanaUlBari_0-1673441023626.png

 

Thanks in Advance

Sana

8 REPLIES 8
ShankerV
13 - Pulsar

hi @SanaUlBari 

 

Please find the expected solution below.

 

ShankerV_0-1673442200827.png

 

Input was:

ShankerV_1-1673442221554.png

 

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

 

 

Many thanks

Shanker V

ShankerV
13 - Pulsar

Hi @SanaUlBari 

 

The workflow is created to handle the future months scenarios too.

Tested with example below:

ShankerV_0-1673442362235.png

 

Input is:

ShankerV_1-1673442383166.png

 

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

 

Many thanks

Shanker V

 

ShankerV
13 - Pulsar

Hi @SanaUlBari 

 

Step 1: Input tool

ShankerV_0-1673442459973.png

 

Step 2: Record ID tool

 

ShankerV_1-1673442480776.png

 

ShankerV_2-1673442488512.png

 

step 3: 

 

ShankerV_4-1673442513813.png

 

ShankerV_5-1673442527947.png

 

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

 

Many thanks

Shanker V

 

ShankerV
13 - Pulsar

Hi @SanaUlBari 

 

Step 4: Tile tool

ShankerV_0-1673442591443.png

 

ShankerV_1-1673442604796.png

 

Step 5: Sort

ShankerV_2-1673442632513.png

ShankerV_3-1673442641147.png

 

Step 6: Multi Row Formula

ShankerV_4-1673442666141.png

 

 

if [Particulars]="Opening" and [Tile_SequenceNum]>1
then [Row-1:Value]
else [Value]
endif

 

ShankerV_5-1673442685602.png

 

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

 

Many thanks

Shanker V

ShankerV
13 - Pulsar

Hi @SanaUlBari 

 

Step 7: Cross tab tool

ShankerV_1-1673442770040.png

 

ShankerV_2-1673442787213.png

 

 

Expected Result:

 

ShankerV_3-1673442819974.png

 

 

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

 

Many thanks

Shanker V

 

 

SanaUlBari
6 - Meteoroid

Hi Shanker

 

Thanks so much for your help! I am just stuck on the last step where in Crosstab i cant see option to concatenate

SanaUlBari_0-1673450644902.png

SanaUlBari_1-1673450670683.png

 

Am i using the correct tool?

 

SanaUlBari_2-1673450773549.png

 

Thanks

Sana

JamesCharnley
12 - Quasar

@SanaUlBari the list of aggregations you'll see in the crosstab tool depends on the data type of the field you're looking to aggregate. You're seeing numerical functions so I can tell the field is numerical, whereas concatenate is applied to strings. If you want to concatenate you'll need to change the data type of that field pre-crosstab.

SanaUlBari
6 - Meteoroid

Thanks @jamesCharnley - This makes sense now

Labels