Coding Help

[ayadav8]

Hey,

I need help to achieve desired result out of the below shown data:

Requirement

1. Fill "Rolling Total" column : whenever value<threshold, Rolling total = [Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value] (if threshold = 3)  i.e addition till threshold value.

2. Also need to make sure it shouldn't add value from other "Name". meaning, no value from "Dog" to be added in "Cat".

3. In case there are no enough data to reach threshold limit, it should add all the next available values. For example. If my threshold = 4 but I have only 3 further value points, it should add all three next values.

I am using the Multi-Row formula with below code but it is adding values from "Dog"

If [Name] = [Row+1:Name] then

   If [threshold] = 3 then

     [Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value]

   else if [threshold] = 4 then

   [Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value] + [Row+4:Value]

else

0

endif

else 0

endif

I am aware that the start of the code is incorrect but I am not sure how do I change it to get desired result.

[SophiaF · Accepted solution]

The 'Group By' functionality in the Multi-Row tool should be able to help you. If you group by 'Name' it will only run the formula on the subset of records for 'Cat' and 'Dog' respectively. You should then be able to remove the [threshold] value from your conditional to make it simpler.

[DataBlender]

I went about this a different way, and it might be using a sledgehammer to crack a nut but..

Attached is a workflow which has a macro to set the data up for another iterative macro to then run through.

It follows similar logic to what you've set out below but rather than having to code it using a multi-row formula, it does it by numbering the rows in the group and then iteratively choosing the n rows that are required to sum up, e.g. rows 1-4 for Dog and 1-5 for Cat.

Then it moves on to the second row as the starting point (2-5 for Dog, 2-6 for Cat) and does the same thing until it runs out of rows.

[ayadav8]

@SophiaF - This helped. Thank you so much!!

[ayadav8]

@Bolide - The macro looks impressive but the output is incorrect. I want "Rolling Total" to be updated only in the case Value<threshold. If Value>=threshold it should output zero. Can you update the macro or guide me where to apply this condition??  I really look forward to using this macro.

thanks!!

[DataBlender · Accepted solution]

Hi @ayadav8, try this one

[ayadav8]

This is awsome.. Thanks!!!