I have a date that includes various fields, but for this particular task, I’m focused on the columns related to projects, resources, Program Increments (PI), sprints, dates, and closed points. I also have other fields in the dataset, but they are unique to these columns and aren’t relevant for this specific calculation. My goal is to compute the sum of unique story points for each combination of program, resource, sprint, and PI using Amazon QuickSight. Please refer to the attached input and expected output for more details.
Hi @hellosai321
maybe you can use sumOver
BR
Hi @ErikG ,
It calculates sum of all but in my scenario I would need sum of unique numbers for group.
Could you pls create a sample, based on you screenshot, what the expected outcome would be?
Last column is the expected output.
Ok. But how do i find the unique points?
It looks like 10+10+9 but the 9 is on the same sprint as 10 bevor.
For example sum of unique values for sprint 5 is 10 and sum of unique values(10+9) for sprint 4 and sum of all this will give 29 for a resource John.
HI @hellosai321
i did some calculations but one step is missing.
But is it in the right direction?
BR
Hi @ErikG ,
I think It is in the right direction. Now, my scenario would need a sum(lag_value) per each resouce.
Thanks
ok. i will see.
in the meantime what type of datasource are you using?
I’m using athena dataset and need those calculation in quicksight.
But you could performe the calculation in the datasource (SQL)?
I will do the calculations in Quicksight and we don’t have an access to alter athena queries for this dataset.
Could you create calculated fields on dataset level? I don’t think we can avoid that.
lagValue_DS = ifelse(ifelse(lag(sum(closedpts),[date DESC],1,[Sprint,Rescoure])=sum(closedpts),0,1)=1,sum(closedpts),0)
sumlagValue_DS = sumOver(lagValue_DS,[Rescoure]
Hello @hellosai321 !
Was @ErikG 's suggestion helpful, and if so could you mark their comment as a solution to help the community?
It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 3 business days this topic will be archived.
Hi,
This solution is not giving me the expected output and it is giving the same output as Sum function does…
Hi @hellosai321
that’s awkward. As you can see it worked for me. Did you check the partition part? Could you rebuild the lag_value column?
BR
Hi @hellosai321
any update on your side?
BR