Hello all,
I'm trying to edit a feed but I have problems with calculations that I want to do on the data source. My data source is a simple excel file with two columns as depicted below:
In the feed I want to calculate for the amount, what percentage b+c make out of the Total.
In other words, I want to calculate this: ((b+c)/total)*100%. Right now I perform the calculation in excel and use the result in Mashzone but I expect the calculation to be possible in Mashzone as well.
Any help is welcome I have tried many combinations of operators but I don't seem to figure this one out.
Thanks, Roy
Hi Roy,
of course MashZone can help you. There are actually two ways to achive this. The first is rather simple and assumes that -- as in your example -- each amount represents percentages already, i.e. total remains 100 constantly.
So finding the percentage of row b and c is a question of filtering for only this rows and sum up their amount values. The example depicted below uses the filter operator to select the proper rows. The aggregation is done during data assignment -- a plain text field consists of two parts: "Percentage of b and c: " and the sum of all values found in the amount column.
.png)
The second one becomes useful when the total is something different from 100. In this case we need a data feed operator which is called Goal accomplishment. Practically it has three inputs.
- All the records to calculate Goal accomplishments (connector point on the upper side of the feed operator)
- One single value to know what value represents 100% - we will determine this dynamically. Connectors for single input values reside on the right hand side of the operator.
- Same as before but for the lower bound - what represents 0%. We will constantly assume 0, but it works as above.
So to do Goal accomplishment we need two distinct information from your feed. Therefore we create a copy of the data read at the beginning. This is done using the data feeds copy operator placed directly after the data source. Now we have up to four parallel paths of calculation.
The sum is determined dynamically on the right hand side. We use the operator Calculation > Aggregation to sum all values found in the column amount. This results in a feed with one column and row (yes aggregation is tricky). The operator Values > Determine value converts this one cell feed into something we can use later. Please note: the determine value operator has no output connector on its bottom. It just converts multiple records into single ones.
On the left hand side Goal accomplishment has been inserted. It is configured as described before. The edge highlighted provides the dynamically calculated total. (I modified the test data to get meaningful values in this case.) The filtering and rest you already know.
.png)
