I have created a below view, which has the raw data of the dataset and an extra column has been created as rank, which ranks based on prospectid,month and ordered by the date desc,
In my context here,for example when I select the filter of start day and end day as 1 and 15 then for that prospectid, for that selected month the 15th day will be getting rank=1,
Now I want to create an aggregate view for only rank=1, i.e at LC level now I want the total prospectids as an aggregate etc,.
Hello @kariappa, welcome to the QuickSight community!
I think I might need a little more information to totally understand the functionality you are wanting to implement. What I will so though, is you should try utilizing the denseRank function in your calculated field to create a rank on each row that should update depending on the filters you are applying to the visual. Then you should be able to apply more filters based on the rank that is being attached to the row columns.
I think with some of your expectations, you will likely need another visual to change the way your visual displays based on your selection. Depending on your partition though for the rank calculation, you can create another calculated field to count the number of prospect_ids based on the rank they are given. Something like this:
rank1Prospects = ifelse({rank} = 1, {prospect_id}, NULL)
Then you would be able to run a distinct count on the prospectIds that are being returned. Let me know if this helps!
Hello @kariappa, since we have not heard back from you I will go ahead and mark my response above as the solution. If you have any follow-up questions on this, please let me know and I can assist. Thank you!
