I used the original dataset to create many calculated fields and a pivot table. But when I try to build other visuals like line graphs using these calculated fields, I couldn’t get any result. Is it possible to build new graphs on top of the pivot table?
You cant directly use the result of a given visual as a new data set, however you could export the data from that visual to excel/CSV and then upload that as a new data set.
Alternatively (and probably the better way) maybe we need to adjust your calculations so that you can use them in other visual types. Can you describe what calcs you are doing?
I have a dataset with all the transaction info (customer name, transaction date, price…). The first record of a customer would be counted as a new user. And if there is no record on the next month, this means the customer leave. How can I calculate how many new customers and how many customers lose in each month/year?
For now, I’m using minOver() to find out the first date, and if it is not null, I will assign a ‘1’ to this record in a new column. Then using the pivot table, I could sum all the ‘1’ in this column. I also found that Quicksight doesn’t allow me to directly compare the date after calculation (using minOver) with the date from the original dataset. That’s why I made the whole calculation so complex.
I think some small changes to your calc will allow you to do this. MinOver can act like a Table Calculation (result is an aggregated field, and is computed locally after the query is run), or a Level Aware Aggregation (result is a non-aggregate field, and is sent in the query to the DB). The syntax is just slightly different:
minOver(date field, [customer name field], PRE_AGG)
Then you can do row-level comparisons with the actual date field like:
ifelse(date field = first transaction per customer calculation, 1, 0)
Then can use it as a filter, or sum the value, use it in other visual types, etc.
For more info on LAAs, see this blog:
That would be awesome, thanks for your help!