I have created a view in Quicksight with the raw data and have created a calculated field for ranking the rows group by on prospectid,month ordered by the activity_date desc.
When the user has selected month=1 and startday=1 and endday=23, then for a particular prospectid, month the 23rd day will get a rank=1.
So each pde_lc in the data will have multiple prospectids associated with him/her.
Now I want to keep only rank=1 records and then use other columns for aggregation at a pde_lc and month level.
so my final output will be having columns such as
pde_lc, month and then some aggregated columns will be created with the help of calculated field with the help of columns which is present in the 1st attached snap.
Based on the 1st attached snapshot, I want to create an aggregated view at pde_lc and month-level(for only rank=1).
The final output will look like
Here the team_owner is pde_lc.
Total PDE leads - count of distinct prospectids for the selected team.
enrolls - count of distinct prospectids for the selected team where the column Enrol=1 in the first view. etc.
@kariappa were you able to try @ErikG 's suggestion above, and if it worked could you mark their comment as a solution to help the community?
It has been some time since we have heard from you but we would still like to help find a solution. If we do not hear from you in the next 2 business days this topic will be archived.
