How to sum selective columns based on data availability

[MonikaBhargava]

Hi ,

We have month wise data in excel sheet available. The data keeps changing with every month as each month passes. So we have two steps to do 

1. Column 1: Cumulative sum of all the months till date. So if it is March we need Jan + Feb +Mar 

2. Column 2: Cumulative sum of all the months excluding current month. So if it is March we need Jan +Feb 

We want a formula wherein Alteryx detects the last column with data and create sum for Column 1 and create column 2 excluding the last month. 

Currently we have to go and manually change the formula for both column 1 and column2. This would be an issue when we will schedule the Alteryx models for run through server at designated time 

[jamielaird]

Hi @MonikaBhargava ,

Here's one way you can do it:

Since your source data only included values for Jan and Feb I added dummy values for Mar to make it easier to demonstrate how this would work.

The core of it is that after transposing the data into a two-column format, we use two formulas to populate the value for either all months or all months except the current month. There is some date parsing involved but essentially all we are saying is:

- If the month of the row is less than or equal to the current month, return the value, else return Null

- If the month of the row is less than the current month, return the value, else return Null