Caluculated field - Addition of two column values in Analysis

Question & Answer
, , , , , ,
1

I am trying to create a calculated field in Analysis section. The 2 original column names are: purchase, bookings.

I have used following formula but nothing is working:

  1. sum({purchase}) + sum({bookings})
  2. {purchase} + {bookings}
  3. (sum({purchase}) + sum({bookings}))
2

Hi @Gaurav_Shukla - Welcome to AWS QuickSight and thanks for posting the question. Can you please give sample data and data types, this will help in guiding you the right solution.

Regards - Sanjeeb

3

Hi @Gaurav_Shukla

both columns are numbers?
What error is the formula editor throwing?

BR

4

both columns have decimal (float) values

5

Hi @Gaurav_Shukla - Please give sample data.

Regards - Sanjeeb

6

yes, both column are numbers. No error in formula.

image
image1920Ă—610 25.6 KB

7

I don’t think I can upload data file here. I created a sample in csv / excel format

8

One more thing
after saving the formula, Quick Sight is automatically removing curly braces for one of the column “purchase”. That is why there is no aggregation level showing up, it is coming sup as “custom” aggregation level.

image

9

https://docs.google.com/spreadsheets/d/1oRvTXE6G4qTsEx5WGJYcmEGL9-9eDfE-SxLxvGHw3Sg/edit#gid=0

This is google sheet of sample data.
I want to sum last 2 columns in “analysis” section before publishing the Dashboard

10

Hi @Gaurav_Shukla - I do not have access. Can you please grant the access and I assume that the data is not contain any sensitive or confidential info. If it has, then do not share.

Regards - Sanjeeb

11

access granted for the google sheet.

1 Like
12

are you getting an error in formula editor or is QS just not calc anything?

13

I am not getting any error.
QS is not calculating anything.

14

even for {purchase} + {bookings}?

could you pls create a table visual with {purchase}, {bookings} and calc field {purchase} + {bookings}?

15

I tried that…It is not showing values

image
image823Ă—85 5.77 KB

16

just for “fun” could you try total_cost + impressions and purchase + total_cost

17

I tried “total_cost + impressions” , it’s giving correct value.
I guess it is then the issue of blank cells. It is not considering 0 and instead might be treating it as “Null”. Can that be the case?

18

ok good.
try ifelse(isNull({purchase}),0,{purchase})+ifelse(isNull({bookings}),0,{bookings})

2 Likes
19

QS is not calculating because the values in your both columns are not in same row. If you need third column with both the values use this:
ifelse(

isNull({purchase}) , {bookings} ,

isNull({bookings}) , {purchase} ,

{purchase}+ {bookings})

20

Worked…awesome…!!
Thanks a ton Erik :slight_smile: