How to add rows with a formula for different months and split the amount?

Hi everyone,

I have a query I am unable to figure out, really appreciate anyone's help!

I wish to break down a period > 1 month into different rows and simultaneously also break down the amount.

While i understand the initial step would be to split the months and convert them into alteryx readable date format, not sure about how to proceed.

Here is the Input and the intended Output data respectively (also attached the excel file):

(also, i cannot perform dragging vendor name down using a multi row formula on the entire data set as few line items in between are meant to have no vendor names)

Vendor	Period	Amount
A	Apr 22 - Jun 22	90
B	May 22 - Jun 22	30
C	Oct 22 - Dec 22	120

Vendor	Period	Amount
A	Apr-22	30
A	May-22	30
A	Jun-22	30
B	May-22	15
B	Jun-22	15
C	Oct-22	40
C	Nov-22	40
C	Dec-22	40

Alteryx Query.xlsx

Date Time

Optimization

Accepted answers

Felipe_Ribeir0

Hi @pkmc

One way of doing this

How to add rows with a formula.yxmd

All comments

IraWatt

Hey @pkmc,

Here is one way:

I use the sample tool to get the first and last period for each vendor then used a summarize tool to concatenate them. I then joined that with a summary of vendor and ammount.

Workflow.yxmd

Felipe_Ribeir0

Hi @pkmc

One way of doing this

How to add rows with a formula.yxmd

pkmc

Hi @IraWatt
in your solution the input and output have been reversed
so the input is the first table in the query where the period is Apr 22 - Jun 22 and so on
and the table below in the query is the intended Output

Felipe's solution has worked
Thank you for your efforts, really appreciate them!

pkmc

@Felipe_Ribeir0

Thank you so much brother, really appreciate it!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703