community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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-MonthClientProductInitial StockPurchasesSalesFinal Stock
201601VeronaKetchup1,261557252,2331,6211161,87344125
201602VeronaKetchup1,873441251,7861,3757432,28369825
201603VeronaKetchup2,283698252,2331,3013043,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 :)

 

Multistock.PNG

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

Alteryx Certified Partner
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?

 

ConcatenateYear-MonthClientProductInitial StockPurchasesSalesFinal Stock
201601VeronaKetchup201601VeronaKetchup1,261557252,2331,6211161,87344125
201601Dressings SAKetchup201601Dressings SAKetchup1,567882,561,6211162,506764
201601Dressings SASavora201601Dressings SASavora2,4572,3331,6211163,168884
201602VeronaKetchup201602VeronaKetchup1,873441252,2331,6211162,48532525
201602Dressings SASavora201602Dressings SASavora3,1688842,2331,6211163,780768
Alteryx Certified Partner
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


Data Lover

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