Auto calculate last 12 columns

Hi All, I need assistance with a workflow that auto calculates the last 12 columns every month. I have a report with full year results by months but I only need to sum the last 12 months. Every month a new column is added.

For example, I need to sum columns K through V for this month. Next month I need to sum columns L though W. I've attached an sample file.

Any advice is much appreciated.

Thanks!

Auto calculate last 12 columns.xlsx

Designer Cloud

Accepted answers

Qiu

@TB_Mylene
We can do a Transpose first, then order the columns in a decending order and select the top 12 months.

1006-TB_Mylene.yxmd

All comments

Qiu

@TB_Mylene
We can do a Transpose first, then order the columns in a decending order and select the top 12 months.

1006-TB_Mylene.yxmd

flying008

Hi, @TB_Mylene

Another way is a dynamic solution for you :

录制_2023_10_06_16_19_51_734.gif

******

@Qiu
I have discovered an unimaginable phenomenon, There are 3 fields like [A] [B] [C], if you want to [C] = [A] + [B] , now [A] = 3, but value of [B] is null, then the [C] get a null result after run ! What's wrong ? (this is reason of must use Multi-Field Formula tool in above workflow.)

TB_Mylene

Thank you so much! I think this may work. I will try this out and let you know.

Qiu

@flying008
I am always not quite get the "Dynamic Replace". Thanks for great sample.

And you are right.
At least in the Formula tool if you add one numeric valve to a Null value, the result will be Null as well.

But somehow it works for the Summarize tool but We shoul definitely add one Cleasing tool to be safe.

Replace null values with blanks in the case of text fields and change nulls to zeros in the case of numeric ones

2023-10-07 075257.png

1006-TB_Mylene-r1.yxmd

flying008

Hi, @Qiu

Thank you for your reply ! IMHO, 1 + Null() = 1, but the formula get result is Null, so what is the reason (even in v2023.1)?

TB_Mylene

@Qiu your suggestion worked within my workflow. Thank you again!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703