cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## Stock Formula and Base

Meteoroid

Hello Everyone,

I have to get a calculated stock value, for a stocks report.

Im doing it in excel, but im just trying alteryx like a new tool for me and i would like to automatize this processs with alteryx.

I couldnt get it , maybe somebody can helpme here.

Here we are :

Im Using this common formula : Initial Stock + Purchases - Sales = Final Stock
Then , the final stock in one month must be the initial stock for the next month, and here is where i have the problem.

I have 3 bases o 3 dates :
-Initial Stocks
-Purchases (Sell In)
-Sales (sell out)

Im doing it in excel :

 Year-Month Client Product Initial Stock Purchases Sales Final Stock 201601 Verona Ketchup 1,26155725 2,233 1,621116 1,87344125 201602 Verona Ketchup 1,87344125 1,786 1,375743 2,28369825 201603 Verona Ketchup 2,28369825 2,233 1,301304 3,21539425

First Month Initial Stock :
is date by VLOOKUP , only for the first month in the base
Purchases :
is date by VLOOKUP
Sales :
is date by VLOOKUP
Final Stock :
Initial Stock + Purchases - Sales
Initial Stock :
Final stock value of previous month
I use a vlookup of concatenate
Month-1+client+Product

Using alteryx i cant elimite the "VLOOKUP" proces using the "union tool"

but how do correctly that the initial stock have to be the final stock of previous month in alteryx and get an flat base?

Sorry for my english and if i couldnt be clear.

Alteryx Certified Partner

Hey @Nicoiba

Welcome to the magical world of MultiRow Formula! My most favourite tool :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Alteryx Certified Partner

It is possible to achieve this using the multi-row formula tool and then a standard formula tool.

Attached is the solution.

Ben

Highlighted
Meteoroid

Thanks!

its Works if i have a lot of clientes and producs in the base?

 Concatenate Year-Month Client Product Initial Stock Purchases Sales Final Stock 201601VeronaKetchup 201601 Verona Ketchup 1,26155725 2,233 1,621116 1,87344125 201601Dressings SAKetchup 201601 Dressings SA Ketchup 1,56788 2,56 1,621116 2,506764 201601Dressings SASavora 201601 Dressings SA Savora 2,457 2,333 1,621116 3,168884 201602VeronaKetchup 201602 Verona Ketchup 1,87344125 2,233 1,621116 2,48532525 201602Dressings SASavora 201602 Dressings SA Savora 3,168884 2,233 1,621116 3,780768
Alteryx Certified Partner

I believe in both myself and @LordNeilLord solution you would simply need to place the client and product as 'group by' criteria in the multi-row formula tool.

Ben

Alteryx Certified Partner

Hey @Nicoiba

@BenMoss is correct...we both made use of the "group by function" to allow for different customers and products.

Ben pointed out that my workflow wasn't quite correct so here's the correction (which is the same as Ben's)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Meteoroid

Hi guys! thank you so much for your help!

Well.. i tried the formul with my real bases and i couldnt get it... dont know why.

I attach an example (when i use the "text input" the multiwor formula show error, dont know why).

what do you think?

Im trying to do it in Excel to... but when i try to do a similar "multirow" with other concatenates and ways in a base with almost 100.000 registers , Excel crush.

Alteryx Certified Partner

You get the error because the values you are using in the multirow are appearing as string fields. If you convert all variables used within the multirow to numeric type then it should work correctly.

Ben

Meteoroid

Thank you Ben , i already changed the format to number, buy anyway is not working the row formula. i mean, i cant see values in the "stock inicial 2" created in the row formula and the "stock Final" at the end.

Labels