Inconsistencies in KPI figures

Question & Answer
,
1

Hi,

I’m running into a problem when using a calculated field in a KPI figure.
My dashboard uses parameters that I will call X, Y and Z. I have multiple columns in my dataset that I will denote by numbers 1, 2, 3 etc. I have a calculated field that is set up like this:

value / 
(ifelse(
(isNull(X) AND isNull(Y) AND isNull(Z)), 1,
(isNotNull(X) AND isNull(Y) AND isNull(Z)), 2,
(isNull(X) AND isNotNull(Y) AND isNull(Z)), 3,
(isNotNull(X) AND isNotNull(Y) AND isNull(Z)), 4,
#plus other combinations of isNull/isNotNull
))

I have it set up this way as I need to divide a column by a different denominator depending on the selection of parameters the user has made. However the value that is displayed by the KPI is inconsistent. For example, when no parameters have been changed, sometimes 33% is displayed, sometimes 40%, sometimes as 25%. I’m a bit lost as to why the value displayed is inconsistent, any help would be greatly appreciated.
Thanks

2

Hi @Liam

Can you share more details on when you see different KPI values?

Also, where doe the value in the numerator come from?

3

Hi Max,

So the numerator is just a boolean in my dataset, 1 or 0, and the KPI is set to Sum and display as a percentage with no decimal places.

I’m seeing different values at random times just when I open the analysis/dashboard - the parameters are all set to the same values, I’m not sure why or how there are inconsistencies in the data displayed.

I’ve tried doing the division in the SQL and returning columns of just the decimals, and then selecting a column to visualise based on the ifelse statement in my post and I run into the same problem - inconsistencies in what the KPI is showing.

4

Hello Liam,

The fact that you reproduce the issue using a SQL editor tells me that there is something wrong with the approach but not with Quicksight.

In your test with SQL, you should end up with a dataset made of columns X, Y, Z ,1,2,3,… + an extra column, “KPI”, with your computed value with the ifelse (I guess a “case” in transact-sql ?). Do I get it correctly? In this scenario, are you saying you don’t get the same KPI value for each row each time?
Or are you trying to compute a unique aggregated KPI for your set of rows? In such case, you would have to use an aggregation function (sum, min, max,…).

Just trying to help…

G.

5

@Liam

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

6

Hi @Max

I have engineered a different solution in my SQL to achieve what I needed to do so I’m no longer using the ifelse statement in my post - more than happy for this thread to be closed down

1 Like