SJ
Hi all, this is my first entry (sorry by my bad english).
I have a xls file with 2 keys and 2 values like this:
Key 1 Key2 Value1 Value2
K11 K21 10 20
K11 K22 15 30
K11 K22 11 44
K21 K23 7 40
I have a mash to select Key1 and key2 (filters) and the result as for the filter:
- r1:sum(value1)
- r2:sum(value2)
- r3: ratio: sum(value1) / sum(value2) (obviusly after apply the filtres via mash)
What as the way to create this ratio (r3)?
Sample of desired result
if I filter for K11 and K22 (using combos) I want:
r1 = 15+11 = 26
r2 = 20+44 = 64
r3 = 26 / 64 = 0,040
I agree your help!
Hi all again, i find one solution at this post from Dr. Tobias Blickle , Thaks Tobias.
If exits other solutions I agree more comments.
Thanks a lot
Santi.-
Santiago,
as already suggested the proper way to do calculations is within the data feed editor. When assigning data within the visual composer aggregation is supported since a strategy to deal with multiple row results is required.
So inserting a column which contains the ratio of v1 and v2 for each row and aggregate later seems the first suggestion. But as sum/div is not distributive we need to do filtering, aggregation and calculation manually to preserve the proper order.
You found the answer yourself as Tobias' post describes the important things, already. Maybe it is useful to highlight how filtering works, as I assume you need the current combo box selections within your data feed to filter for k1 and k2. Please find an overview below.
.PNG)
The example makes use of user input fields. Inputs are single row/column, i.e. cell, variables to be inserted anywhere within your feed processing. Cell values are docked on the right hand side of the feed operators. If you use the data feed depicted above when assiging its data to a MashApp it looks like this:
As you can see the data feed consists of three rows and two user input fields. Binding columns to charts or tables you already know and binding your current combo box selection to the input works similiar to filtering. Hit filter data (which is named confusingly, I admit) and it looks like this:
.PNG)
Select the component where the selection event originates and specify you want to use it as input into the feed instead of filtering and you are done.
Regards
Stephan
Hi all again, i find one solution at this post from Dr. Tobias Blickle , Thaks Tobias.
If exits other solutions I agree more comments.
Thanks a lot
Santi.-
Hi all,
this topic might be a little bit older, but I actaully have a similar problem working on my first mashup!
If I calculate a ratio or quote in my datafeed, how could I display it in a table the right way, regarding that the data in the table might be selected by a user input field, as Stephan described in his posting?
Which setting to display the ratio-field is the correct one? The ways of aggregation are AVG, SUM, MIN, MAX and CNT. But nothing leads to the correct result when using a quote or ratio.
The calculation depends on the selection of the depending fields/values.
Where is my mistake?
Micheel,
based on your description it is hard for me to help you, please explain in more detail where MashZone behaves not the way you expect it.
Displaying values works always the same, regardless if in tables, labels or charts - even if filtered by selections. Please post your data feed's result to give me an idea what you are trying to accomplish.
Hi Stephan,
I will try to detail it. My MashZone is in german and I´m not sure how to switch it into english. As a result my screenshots are in german. Sorry for that.
My MashApp is just an example to discribe the problem.
I use this excel sheet for my datafeed
My datafeed looks like this:
In my MashApp I created three tables. The table on the left hand side is without selection parameters. The table in the middle is selected by person an the table on the right hand side is selected by period (month).
The settings for the Utilization column is allways the same (average):
The results in the table on the right hand side is correct, but in the middle they are not.
For example Person A has 24 confirmation days in 39 workdays. That´s a ratio of 61.5%. But MashZone calculates 60.65% because of the fact, that it averages the results of the single rows in the datafeed of Person A.
I hope my problem is a little bit more comprehensive right now.
Thanks for your help.
Cheers,
Jan
