Hello
Please can you help me understand why parsint is providing me incorrect values. My goal is to convert the study statuses for courses to an integer so I can count the number of people have have completed all courses. The current process I’ve used might not be the best way to achieve my goal, please let me know if there is a better way to achieve my goal.
My current process
- I use an ifelse function to check the course status and give it a number.
- Next I convert the number to an integer so I can count it.
- In this example the result for ‘Courses Complete int’ is 9, but it should be 2.
I’ve created another table showing the user has only completed two courses, Olympus and Mercury.
Thank you in advance for your time.
Kind regards
Robert
@Robert_Amey Create a calculated field on your parseInt as: sumOver(Course Complete int, [User FullName], PRE_AGG)/countOver(Course Complete int, [User FullName, Course], PRE_AGG). Let’s name this field to be “completed”. Put “completed” into the table, let the agg function to be Min, or Avg:
It should be fine.
You get number 9 because in your datasets, there are duplicate rows for every pair of User Full Name and Course. You might have other dimensions besides these two fields. The “Count” is counting these duplicates.
Hello Ying_Wang
Thank you for the formula it works great. Could you help me with the count. I have some more examples in the picture below.
In the first two highlighted, ‘courses complete int’ and ‘courses complete int 2’ should both be 0 as no courses for Olympus, Atlas or Mercury have been completed.
In the last highlighted, ‘courses complete int’ and ‘courses complete int 2’ should both be 2 as only course for Olympus and Mercury have been completed. I don’t understand why it counts 5 for each for this person and why the count is correct for other people, but not everyone. Most uses only have 3 rows in the source spreadsheet.
Sorry for the delayed response.
Kind Regards
Robert
@Robert_Amey Sorry for response late. I was in a business travel these days.
The simplest way to test it is: put all the fields of the dataset into the table, apply the filter on the problematic user. And then, you will see which fields producing these duplicates.
After then, divide the original counts by the count of the fields producing duplicates. You will be fine.
I know, QuickSight should provide the row count function to address this issue. However, later this year we will have a new feature to run a better aggregation which you will not see the duplicates anymore. Thanks!
@emilyzhu @hafeng FYI
