Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

total by row on x number of columns

7 - Meteor

Hi

 

I need to created a Total Revenue column that totals by Row. Each column represents a month. However, this changes each fiscal year (ours is from Oct-Sept). In the attached file, I've put all the months and at the moment, we calc this in a formula

 

(if IsNull([Oct. 2017]) then 0 else [Oct. 2017] endif) +
(if IsNull([Nov. 2017]) then 0 else [Nov. 2017] endif) +
(if IsNull([Dec. 2017]) then 0 else [Dec. 2017] endif) +
(if IsNull([Jan. 2018]) then 0 else [Jan. 2018] endif) +
(if IsNull([Feb. 2018]) then 0 else [Feb. 2018] endif) +
(if IsNull([Mar. 2018]) then 0 else [Mar. 2018] endif) +
(if IsNull([Apr. 2018]) then 0 else [Apr. 2018] endif) +
(if IsNull([May 2018]) then 0 else [May 2018] endif) +
(if IsNull([Jun. 2018]) then 0 else [Jun. 2018] endif) +
(if IsNull([Jul. 2018]) then 0 else [Jul. 2018] endif) +
(if IsNull([Aug. 2018]) then 0 else [Aug. 2018] endif) +
(if IsNull([Sep. 2018]) then 0 else [Sep. 2018] endif)

 

But I want to be able to calculate this without specifying the cols as next month, the data will only show for Oct 18 and then increase by each month till the end of the year.

 

How can I do this?

16 - Nebula
16 - Nebula

One solution, is to use a record ID and then transpose to switch to rows. You can then summarise and join back.

2018-10-23_11-18-25.png

 

Sample attached.

Labels