Store value of calculated field as a Number

Q&A
,
1

Hello everybody!

I have a Quicksight dataset that looks like this:

Customer ID / Deposit
1 / 100
69 / 460
366 / 780
432 / 220
987 / 354
1089 / 65

I would like to create another column that tells me if the Customer deposited more than the 25% percentile of total Deposit amount.

I create the calculated field Percentile:

percentile(Deposit,25)

In our example, the 25% percentile is 100. Now i create my field:

image
image922×167 6.5 KB

But it gives me an aggregation error. If i replace Percentile by 100 it will work, but i want this field to be dynamic.

Any idea on how to do this ?
Thanks!

2

Hi @Jimmydean
you could try to do something like

ifelse(sum({Deposit})>=sum({Percentile}),1,0)

BR

3

Hello Erik ! Thanks for your answer
I’m getting this error trying this:

image
image918×174 8.39 KB

4

Hello @Jimmydean and @ErikG !

@Jimmydean are you still working on this issue or were you able to find a solution? An easy way I have been able to get around this in the past is moving an aggregated portion of my conditional statement to the dataset layer. Then, recreate the calculated field as you have it now.

This topic will be archived in 3 business days if there is not new activity.

5

Hello Duncan!

No, nobody was able to help me on this

6

Hey @Jimmydean, have you tried moving the percentile calculation to the dataset level?

7

It was not working because the deposit is not aggregated and percentile is aggregated. Like the error message described.

You can use table calculation function like below script
percentileDiscOver(sum(Deposit),25,[])

and IfElse
ifelse(sum(Deposit) > percentile, 1, 0)

image