Hello, I have customerid, purchasedate, amount columns. Some customers have multiple rows. I would like to get amount for each customer for their last purchase only on a new column. I thought maxover function would do it but I can’t seem to get last purchase date amount.
1 Like
Hi @SameedAhmed
To get the amount for each customer for their last purchase date, you can use the combination of the MaxOver function and the If function in Amazon QuickSight. Here’s how you can do it:
Create a calculated field to find the maximum purchase date for each customer using the MaxOver function:
MaxOver({purchasedate}, [customerid])
This will give you the maximum purchase date for each customer.
Next, create another calculated field to filter the data and get the amount for each customer for their last purchase date:
Ifelse({purchasedate} = {Max_Purchase_Date}, {amount}, NULL)
In this formula, we are using the If function to check if the current row’s purchase date is equal to the maximum purchase date for that customer (calculated in step 1). If it’s the last purchase date, we return the amount for that row; otherwise, we return NULL.
2 Likes