Is this allowed?
ifelse({kpi}=“A”, sumif({val}, in({kpi}, [“B”, “C”, “D”]), ifelse({kpi}=“M”, sumif({val}, in({kpi}, [“N”, “O”, “P”]), NULL))
It keep throwing mismatch aggregation error.
I tried changing it to -
ifelse({kpi}=“A”, sumif({val}, in({kpi}, [“B”, “C”, “D”]), sumif({val}, in({kpi}, [“N”, “O”, “P”]))
Even then the mismatch aggregation error pops up
Hi @tpanati - Can you please give sample input and expected output. This way community can guide your the right solution.
Tagging some of the experts as well @David_Wong @Biswajit_1993
Regards - Sanjeeb
Sure @Sanjeeb2022 ,
I have a dataset which has KPIs and corresponding Values as two different columns with field names - kpi and val.
I need to perform summation of few of these KPIs to populate another KPI’s value. For example
kpi val
A null
B 4.5
C 7.2
D 3.2
M null
N 8.2
O 9.5
P 3.1
So I wish to use a calculated field to populate the values of these KPIs - A and M:
ifelse({kpi}=“A”, sumif({val}, in({kpi}, [“B”, “C”, “D”]), ifelse({kpi}=“M”, sumif({val}, in({kpi}, [“N”, “O”, “P”]), avg(val)))
But this keeps throwing mismatch aggregation error.
So in order to avoid the avg(val), I chose to display the KPI A and M ( summation Values) as a new pivot table instead -
kpi val_modified
A 14.9
M 20.8
and modified the calculated field as -
val_modified = ifelse({kpi}=“A”, sumif({val}, in({kpi}, [“B”, “C”, “D”]), ifelse({kpi}=“M”, sumif({val}, in({kpi}, [“N”, “O”, “P”]), NULL))
But still I am not able to do away with the mismatch aggregation error.
Can you please help me?
Can you try to calculate A and M separately like this?
A = sumIf(val, in(kpi, [‘B’, ‘C’, ‘D’]))
M = sumIf(val, in(kpi, [‘N’, ‘O’, ‘P’]))
You can then add A and B to separate KPI visuals.
If you want to add them to the same table visual, add them to the Value field well and swap the rows and columns.
