Splitting excel data into quarters

juleshsol

New Member
Hi,

I am importing data from excel into a collection and the data is split into quarters. I wish to add together all those that are Q1, then move onto split all those in Q2 and then add these to a collection which will have the updated totals for each quarter.

ie:
Q1 £250
Q1 £10
Q1 £120
Q2 10
Q2 100

becomes
Q1 £380
Q2 £110

I have put in a loop and it works for the first calculated quarter but then messes up after that. Can anyone help? :)
 

VJR

Well-Known Member
Hi juleshsol,

Your requirement looks to be similar (not fully same) to the one in this link.
You can look at the steps in Post #9 and see if that assists in getting the quarter totals.
Basically, the steps would be:
- Loop through the Main collection shown in your post
- Check if the quarter number (eg; Q1) exists in the FinalCollection using the 'Collection contains value' action. (More on FinalCollection below)
- If it exists then it means you have already totalled up the values for this quarter and then go to the next value of the Main Collection.
- If not, then Filter the collection by the quarter number Q1 of the Main collection.
- This will return another collection lets call it FilteredCollection
Loop through the FilteredCollection and add up the totals.
Once done adding, insert the quarter number and the total to a new collection FinalCollection.
Now your FinalCollection has one row as Q1 £380.

- When the Main loop ends the FinalCollection will have
Q1 £380
Q2 £110
 
Top