Calculated field should be constant if i change the filters

Q&A
,
1

I’m looking for assistance with a filter issue in AWS Quick Sight. I need to apply a filter on the AssignmentName without changing the total number of students in the visual. The filter should only affect the OrgUnitName while keeping the total student count constant, if i select AssignmentName or any other filters and only considering students where IsActive = 1.

Additionally, I’m having trouble with a formula I’m currently using, which isn’t working as expected.
distinctCountOver(ifelse({IsActive}=1,{UserId},NULL),[OrgUnitName],PRE_FILTER)

image
image1312×639 62 KB

2

Hi @Abdul_Rizwan_Mohamme,

When you say your formula isn’t working, do you mean that the count is incorrect or is the count not staying the same when you change AssignmentName and RoleName?

3

I’m currently using the following formula to calculate the total number of students:

TotalStudents = distinctCountOver({UserId}, [OrgUnitName], PRE_AGG)

This formula works as expected when I apply the IsActive = 1 filter. However, when I change the AssignmentName filter, the TotalStudents count also changes, reflecting only the students associated with the selected assignment. I want this TotalStudents number to remain fixed based on the OrgUnitName regardless of the AssignmentName filter.

Alternatively, if I use the following formula

TotalStudents = distinctCountOver({UserId}, [OrgUnitName], PRE_FILTER)

This keeps the TotalStudents number constant when filtering by AssignmentName, but it doesn’t take the IsActive = 1 filter into account.

Could you help me adjust the formula so that it keeps the TotalStudents count fixed by OrgUnitName and consider the condition IsActive = 1

4

@Abdul_Rizwan_Mohamme

Based on what you described, the following should work:

distinctCountOver(ifelse(IsActive = 1, {UserId}, null), [OrgUnitName], PRE_FILTER)

If it doesn’t work, can you show how your raw unaggregated data looks like?

1 Like
5

Hi @Abdul_Rizwan_Mohamme,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this case.

Thank you!

6

Hi @Abdul_Rizwan_Mohamme,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!