How to add extra formulated rows to my column?

Question

Hi,

I am looking to see how can I add formulated rows to my metric column? This data below is a snapshot of the data so there are more rows.

ChannelChannel GroupCategoryMonth TypeSpend TypeMetricValueFiscal YearBranded SearchBranded SearchPaid MediaCurrentDirectInquiry168FY22-23Branded SearchBranded SearchPaid MediaCurrentDirectFT3FY22-23Branded SearchBranded SearchPaid MediaCurrentDirectBudget-INQ435FY21-22Branded SearchBranded SearchPaid MediaActualDirectBudget-Spend34650FY21-22Branded SearchBranded SearchPaid MediaActualDirectBudget-FT33.95081656FY21-22Branded SearchBranded SearchPaid MediaActualDirectSpend34119FY21-22

This is how I would like my data to look like. The "*=" in my metrics columns are the calculations that I would like to have done.

ChannelChannel GroupCategoryMonth TypeSpend TypeMetricValueFiscal YearBranded SearchBranded SearchPaid MediaCurrentDirectInquiry168FY22-23Branded SearchBranded SearchPaid MediaCurrentDirectFT3FY22-23Branded SearchBranded SearchPaid MediaCurrentDirectBudget-INQ435FY21-22Branded SearchBranded SearchPaid MediaActualDirectBudget-Spend34650FY21-22Branded SearchBranded SearchPaid MediaActualDirectBudget-FT33.95081656FY21-22Branded SearchBranded SearchPaid MediaActualDirectSpend34119FY21-22Branded SearchBranded SearchPaid MediaCurrentDirectCEI*=Spend value/Inquiry valueFY22-23Branded SearchBranded SearchPaid MediaCurrentDirectBudget-CEI*=Budget-Spend value/Budget-Inquiry valueFY22-23Branded SearchBranded SearchPaid MediaCurrentDirectInquiry to Start*=FT value/Inquiry valueFY22-23Branded SearchBranded SearchPaid MediaCurrentDirectBudget-Inquiry to Start*=Budget-FT value/Budget-Inquiry valueFY22-23

Is there a way to do this?

Yoshiro_Fujimori · Answer

@bryanmac_92 ,

Your new data is different from the original one.

In the first place, you need to define below conditions.

1. If you want to deal with multiple "Channel", you need to define the grouping rule for each Channel.

It seems to be every 8 rows, but you should define it.

2. Your new data has different "Metric"s, so the original calculation for additional rows is not applicable.

bryanmac_92 · Answer

Hi @Yoshiro_Fujimori

Thanks for this! When I tried to run yourworkflow using my input data that had more rows (x>9000), I get only about 10 rows. Can you check to see why?

Here is my data attached.

Test of Actual Data.xlsx

Yoshiro_Fujimori · Answer

Hi @bryanmac_92 ,

Here is one way of doing this. I hope this helps.

Workflow

Formula tool expressions

CEI = [Spend] / [Inquiry]

Budget-CEI = [Budget_Spend] / [Budget_INQ]

Inquiry to Start = [FT] / [Inquiry]

Budget-Inquiry to Start = [Budget_FT] / [Budget_INQ]

Output

AddFormulatedRows.yxmd