Calculating values from N number of columns

Richard Illyes

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 M1-2-3 added together, but say in June, I'd need to add M1-to-6 added together.)

Is there any way to solve this? (using 2.8.8 at the moment)

Thanks in advance.

Richard

Richard Illyes

Hi Again,

I might have found a solution to this.

1. Columns to rows to get every ToSum column name into Attribute and their values into a Value column.
2. Transform the "Attribute" column to get only the number from the "Tosum1/2/3/4"
3. 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)
4. 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.

Josh Boxer

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

Richard Illyes

Hi Josh,

I attached a file with some sample data.

Thanks a lot for your help.

sample_for_question.xlsx

Josh Boxer

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)

if current month is 9 then sum columns 1 to 8 (ignoring 9 to 12) and if current month is 3 then sum columns 1 to 2, etc.

There is a Function you can use to get the current date https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/create-functions/#dynamic-reference~native-functions

If you can confirm this is what you are looking to achieve (or if not provide some additional clarification please)?

Richard Illyes

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:

if current month is 9 then sum columns 1 to 3 (ignoring 4 to 12) and if current month is 3 then sum columns 1 to 9 (ignoring 10 to 12), etc.

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.

Josh Boxer

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: