Leveraging AGGR function in Formulas

[Christopher_Lawrence]

I am trying to create a certain table inside of sheet that displays all of my assessment names, with the next column showcasing the amount of interviews created in the first stage of the assessment and the following column showcase the amount of interviews created in the second stage of the assessment (Visual of table inside excel sheet under "Trying to Achieve"). I have included some fake, example data in the first tab (Raw Data Example), to which I had wrote an AGGR formula: AGGR(Count(InterviewID), AssessmentName, StageSequence) to get the AGGR table result that is shown in the tab named "AGGR table." However, here is where I get stuck. I am unsure of what else I will need to provide with the AGGR formula to pull in the interview count for the first stage in each assessment. FirstSortedValue will not allow me to leverage a nested aggregate function, so any ideas or suggestions would be very help, thanks!

[skamath1]

You can achieve this with Set Analysis. 

Add the table chart. 

Add Dimension:  Assessment Name

Add a Measure  : Count( {< StageSequence = {1} >} InterviewID)  and label it as  Stage 1 Invited

Add another measure : Count( {< StageSequence = {2} >} InterviewID)  and label it as Stage 2 Invited

[Christopher_Lawrence]

Thank you for providing a great response! I am still trying to get used to the set analysis structure for Qlik Sense so very much appreciated. However, I do have one inquiry. After setting this towards my full set of example data, I am seeing some discrepancies. I have assessments grouped, as well as the full count of InterviewID following this. After, I added in the two measures you had provided. However, upon reviewing these two totals, the do not match up with the total of the full count (Picture attached, 208,317 interviews total, but only 128,538 for the 1st stage and 79,654 for the 2nd stage for a total of 208,192 which indicates a loss of 125 records). What would cause this to occur? I'd assume that if Count(I.InterviewID) provides 208,317 results, splitting it by filtering by StageNumber (I double checked and confirmed there are only 2 stages per assessment), should give me two totals that add up to 208,317 and not 208,192. 

[skamath1]

Do your dataset has StageSequence with blank or Null value? Those will be ignored. 

Are StageSequence and Interview ID in two separate tables? From the screenshot, it has a different Prefix?