Can we calculate the formula using conditions for multiple rows?

[AYESHASIDIKHA]

Hi Team,

I have a doubt, The data I am working on is based on Years and Amounts. Attaching one sample excel file which has dummy data

The condition is like for example,

If we are taking 2023A column then the formula for first and last years(2023,2025) is Amount/2*0.5 and for the middle year(2024)  is   Amount/2.

If we are taking 2023B column then the formula for first and last years(2023,2028) is Amount/5*0.5 and for the middle years (2024,2025,2026,2027)  is   Amount/5.

It would be great if i get a solution ASAP.

[gawa · Accepted solution]

hi @AYESHASIDIKHA 

Please find attached workflow to realize your demand, I understand that you want to calculate in different logic depending on A or B.

If this is OK for you, please accept my post as a solution.

[ed_hayter]

Assuming that your input file had what you wanted the expected output to be like and you only have the year method combo and amount i went about reshaping it as follows

Its a combination of pivots and generating the amount of rows for the period depending on the methodology.

[AYESHASIDIKHA]

Hey https://community.alteryx.com/t5/user/viewprofilepage/user-id/177620

Thank you so much for the quick response. But I need the total formula as well. Could you please help me on that?

[gawa]

@AYESHASIDIKHA 

It is a piece of cake; Just add aggregation option in cross tab. I revised my WorkFlow as attached

[ed_hayter]

When you say totals are you referring to the headline amount number? I added that to our table through a union then stacking the split out values below that intiial row.

[AYESHASIDIKHA]

Hey https://community.alteryx.com/t5/user/viewprofilepage/user-id/420259,

Total Column refers to the sum of entire row. Not the sum of column. If we do the sum of column then the Amount will come right. I need Total which is sum of the Entire row

Thanks in Advance and Thank you so much for help.

[ed_hayter · Accepted solution]

Ive added row totals to my packaged workflow by transposing the data columns summarizing and joining back on to the correct year

[AYESHASIDIKHA]

Hey,

I don't see Total column in the workflow.

[ed_hayter]

Perhaps I attached the wrong packaged workflow.

Done by transposing the data rows and keeping the year column as the key column. Summarize sum(value) grouped by year then join back on on year.