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

[SanaUlBari]

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? 

Thanks in Advance

Sana

[ShankerV · Accepted solution]

hi @SanaUlBari 

Please find the expected solution below.

Input was:

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 · Accepted solution]

Hi @SanaUlBari 

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

Tested with example below:

Input is:

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 · Accepted solution]

Hi @SanaUlBari 

Step 1: Input tool

Step 2: Record ID tool

step 3: 

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 · Accepted solution]

Hi @SanaUlBari 

Step 4: Tile tool

Step 5: Sort

Step 6: Multi Row Formula

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

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 · Accepted solution]

Hi @SanaUlBari 

Step 7: Cross tab tool

Expected Result:

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 · Accepted solution]

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

Am i using the correct tool?

Thanks

Sana

[JamesCharnley · Accepted solution]

@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]

Thanks @jamesCharnley - This makes sense now