Calculated Fields

Hello Community,

I am trying to calculate cell L2 (spend_above_frequency) which is sum of the entire column J minus K2.
Essentially, I want to calculate column L based on columns J(sum) and K(minus one instance/cell value of K).

However, I am not getting the desired output.

Please suggest the function in QuickSight.

Thanks in advance!

Can you do sumOver(sum({j}))-sum({k})?

Hey Max, it doesn’t work. Column K is a running sum of column J.
Gives me an error.
Thanks for looking into it.

can you do this?

sumOver(sum({spend}))-runningSum(sum({spend}),[{frequency_bucket} ASC])

You will need to get the frequency_bucket to be sorted though correctly. So I would look to split on the β€˜_’ and take the second part of it casted to a integer.

parseInt(split(frequency_bucket,β€˜_’,2))

Although one thing I notice is that you have 30+ which would throw an error. So I would then do this.

ifelse(split(frequency_bucket,β€˜_’,2)=β€˜+30’,99,parseInt(split(frequency_bucket,β€˜_’,2)))

I would use this ^ to sort your running sum ASC.

Hey Max,

The formula below worked!

sumOver(sum({spend}))-runningSum(sum({spend}),[{frequency_bucket} ASC])

Thank you :slight_smile:

1 Like