Calculation for repeating data

Question & Answer
,
1

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.

image
image2224×1158 305 KB

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?

2

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.
c_rn

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

Step 2 - Dedup the work articles using that row number

ifelse({c_rn}=1,workarticles,0)

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

sum({c_workarticles_dedup},[{transfer_from_seconary_sub_platform}])

Result:

image
image1085×605 28.6 KB

3

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?

image
image960×366 30.6 KB

image
image1126×350 39.9 KB

4

@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.

Thanks,
Rob