Calculation for repeating data

Hi Everyone, I am facing trouble in creating a calculation in Quicksight which ignores/excludes certain columns. I have my raw data as below where workarticles is an attribute of caseid and hence it is repeating.

I want to create analysis with grouping where # of work articles is calculated based on unique cases falling under that category and taking sum of workarticles just once.

I was able to get this using max(workartciles, [caseid]), but I am stuck in next phase where I don’t want any further addition of columns/drilling to affect this value . As show below, if I drill further, the workarticles that I calculated above should be used based off the first column. Any suggestions?

Hi @Seerat -

Thanks for the question. Today you can do this in 3 steps, but we’re working on getting that number down!

I’ve broken down for the example, but the calcs can be combined to your liking.

Step 1 - Use the rank function to get a row number for your desired level.

rank([{transfer_sequence_number} ASC], [{casei_id}, {transfer_from_seconary_sub_platform}], PRE_FILTER)

Step 2 - Dedup the work articles using that row number


Step 3 - Use a LAC-A function to aggergate at your transferred from level.



Hi @robdhondt , Thank you for the solution. This works. But when I am trying to sort using this field, it throws an error. Do you have any workaround for this?

@Seerat -

Sorry to hear about this. I can’t recreate the issue but I’m looking into it and will let you know if I can provide a workaround for the sort.