Calculating values from N number of columns
Richard Illyes
Learner
Hello.
I have a dataset which contains Keys, some additional important data I'd need to keep and then 12 columns of values (Value (M1), Value (M2), Value (M3) etc.).
Depending on how many months we have left in the year at the time, I have to add N number of columns together (right now, we have October November and December left, so I would need M123 added together, but say in June, I'd need to add M1to6 added together.)
Is there any way to solve this? (using 2.8.8 at the moment)
Thanks in advance.
Richard
0
Best Answer

Richard Illyes Learner
Hi Again,
I might have found a solution to this.
 Columns to rows to get every ToSum column name into Attribute and their values into a Value column.
 Transform the "Attribute" column to get only the number from the "Tosum1/2/3/4"
 The filter is the dynamic part, only keeps lines where the "Attribute" is {Less than or equal to (12  Current month)}. (Need to implement an exception for December, but that should be fairly simple)
 Use group to get the sum of the kept rows' values.
Seems to work OK in my case, might need some tweaking for different calculations.
1
Answers
Hi Richard
Please can you provide a small sample input and desired out (maybe one for June and one for Dec)
If you were to turn the Columns to rows it might make it easier to Sum the values together is my first thought, but not entirely sure without some further detail
Hi Josh,
I attached a file with some sample data.
Thanks a lot for your help.
Thanks for providing the sample which shows 6 columns followed by 12 columns 1 to 12 that need to be summed together.
I think I now understand the question, can I assume that columns 1 to 12 will always be populated and so rather than always summing columns 1 to 12 together, you want to add logic that says (something like)
There is a Function you can use to get the current date https://docs.experianaperture.io/dataquality/aperturedatastudiov2/getstarted/createfunctions/#dynamicreference~nativefunctions
If you can confirm this is what you are looking to achieve (or if not provide some additional clarification please)?
The Tosum columns are always populated, and the value is greater than or equal to 0.
The logic is actually the opposite since we are looking ahead in the calendar until the end of the year and not behind, so:
but this doesn't change much compared to what you wrote, we'd just be using {1 to (12  Current month)} instead of using {1 to (Current month  1)}.
If you can confirm this is what you are looking to achieve (or if not provide some additional clarification please)?
What you described is also fine, I could play around with the rule, my question is more aimed at the way to achieve some kind of dynamic calculation.
The solution with Columns to rows and a Filter is how I would've done it. Interesting to see if there are any other approaches.
For completeness, the Function to get the current month is implemented as follows: