Hello all,
I have an issue with aggregating data within multiple dimensions at once. Please let me know if this is possible at all. I have data set in specific format:
| model | option | option_count | TotalModels | Ratio | Performance | Negative | Negative_sum | Positive | Positive_sum |
|---|---|---|---|---|---|---|---|---|---|
| G | 2VW | 4959 | 50000 | 0,09918 | negative | 1 | 4 | 0 | 5 |
| G | 2VV | 22198 | 50000 | 0,44396 | negative | 1 | 4 | 0 | 5 |
| G | 3AC | 12633 | 50000 | 0,25266 | negative | 1 | 4 | 0 | 5 |
| G | 3DK | 19667 | 50000 | 0,39334 | negative | 1 | 4 | 0 | 5 |
| G | 4NR | 43951 | 50000 | 0,87902 | positive | 0 | 4 | 1 | 5 |
| G | 3AR | 24890 | 50000 | 0,4978 | positive | 0 | 4 | 1 | 5 |
| G | 3M1 | 44625 | 50000 | 0,8925 | positive | 0 | 4 | 1 | 5 |
| G | 5AU | 43792 | 50000 | 0,87584 | positive | 0 | 4 | 1 | 5 |
| G | 2TE | 46311 | 50000 | 0,92622 | positive | 0 | 4 | 1 | 5 |
| U | 2VW | 43534 | 100000 | 0,43534 | negative | 1 | 7 | 0 | 3 |
| U | 2VV | 45802 | 100000 | 0,45802 | negative | 1 | 7 | 0 | 3 |
| U | 3AC | 3737 | 100000 | 0,03737 | negative | 1 | 7 | 0 | 3 |
| U | 3DK | 56766 | 100000 | 0,56766 | negative | 1 | 7 | 0 | 3 |
| U | 4NR | 45361 | 100000 | 0,45361 | negative | 1 | 7 | 0 | 3 |
| U | 3AR | 12162 | 100000 | 0,12162 | negative | 1 | 7 | 0 | 3 |
| U | 3M1 | 44569 | 100000 | 0,44569 | negative | 1 | 7 | 0 | 3 |
| U | 5AU | 94709 | 100000 | 0,94709 | positive | 0 | 7 | 1 | 3 |
| U | 2TE | 50863 | 100000 | 0,50863 | positive | 0 | 7 | 1 | 3 |
| U | 3NR | 60994 | 100000 | 0,60994 | positive | 0 | 7 | 1 | 3 |
For each model, multiple additional options can be selected. I calculate ratio per option and than divide it into ‘positive’ and ‘negative’ categories depending on ratio value. The difficulty here is that user might filter data per country and then all values will change, so all calculations should be dynamic.
The calculations I have for now are the following:
Ratio = sum({option_count})/max(TotalModels})
Performance = ifelse(Ratio>0.5, “positive”, “negative”)
I would like to calculate number of Positive/Negative per ‘model’ and it works fine when I have table with both ‘model’ and ‘option’ as you can see above. However, this value should be presented on bar chart with ‘model’ on x-axis. When there is no ‘option’, the calculation does not work, what can be seen in table below:
| model | TotalModel | Negative_sum | Positive_sum |
|---|---|---|---|
| G | 50000 | 0 | 0 |
| U | 100000 | 0 | 0 |
The calculations I have for now are the following:
Negative = ifelse(Performance=“negative”,1,0)
Negative_sum = sumOver(Negative,[{model}])
Is there a way to calculate number of ‘Negatives’ per model in a dynamic way and show it without additional information about ‘option’?
Number of ‘Negatives’ is dynamic. When user filter for US there might be 5 values, but for Australia 7 values.
Thank you in advance for your support!
