Create runningCount that will not be affected by filter

Question & Answer
, ,
1

Hi!

I have SPICE dataset in quicksight

Dataset contains info about visit history of employees, data was collect using cameras

I created calculated fields

  • top_early_departure
  • top_late_arrival

This columns are rank of employees in the company based on number of late arrivals or early departures. Same logic applied to calculated fields top_early_departure_department and top_late_arrival_department, but in this 2 columns partition by emp_department was applied

QuickSight report designed for getting information for specific employe in given time frames. In report user can choose specific employee by name and surname using QuickSIght filter, but when filter applied, calculated fields also changing, and place in company/department becomes Number 1 (because partition applies only to single employee)

How can I create calculated fields (runningCount) that will not be affected by filter?
Here are my fields which I use to create top_early_departure

field early
ifelse(

  • {Early Departure} = ‘Early Departure’,*
  • 1,*
  • 0*
    )

field top_early_departure
runningCount
(

  • sum(early), *
  • [sum(early) DESC]*
    )
2

Try this:
you alo need to ue partitioned by field.
runningCount
(

  • sum(early), *
  • [sum(early) DESC]* , [company]
    )
1 Like
3

Thank you for your answer, @Shahid_Muhammad
However my question was how to create field (runningCount or any other rank-like function) that will not be affected by analysis filter by employee

4

Hi
Can you please share some sample in arena.

5

Here is link to sample dashboard

RunningCount dashboard

You can see that I have column top_early_departure, which is rank of employee in company based on early departures
If wtry to filter it using filter, you will see that top_early_departure will be calculated based on filtered dataset
I don’t want this field be recalculated
Thank!

6

Hi @alikhan,
Have you tried adding ‘PRE_FILTER’ to your calculation? Check out the below link that explores Level aware calculations a bit more in depth.

Let us know if you have any additional questions!

1 Like
7

Hi @alikhan,
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 from you within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

8

Hi @alikhan,
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!