This has probably been asked and answered before - apologies in advance.
I have data where each row represents a summary of a sequence of actions. There is a unique sequence id, start action, possibly a second action and other actions, and possibly one action that ends the sequence. The data has the sequence id, start time, the number of minutes to the time of the second action where it exists, the number of minutes to the final action where it exists, the number of actions in the sequence.
I have a dashboard with things like median minutes to second action, median minutes to final action, average number of actions
Up to now each sequence has been associated with a single location and so each row has a location field too. The dashboard has a control for a filter on location.
Now this is changing to multiple locations per sequence but the other data does not change at all: now there might be two rows for a sequence with the same values in the number of actions field, the minutes to second action field etc. The only difference between the two rows will be the value in the location field.
How can I change the various calculations to continue to use only one row per sequence?
Hello @PaulPowerAtTeckro, welcome to the Quick Community!
I am curious, are the multiple rows per location mainly causing an issue with calculations? Or is this mostly an issue of how the rows are displayed in a visual?
Also, do you need to display all of the locations in a single field, or is that only relevant because of the duplicated rows?
One good option here to manage this would be to create a denseRank calculation that you can use for filtering. Here are the docs: denseRank - Amazon Quick
It would look something like this:
denseRank([{location} ASC], [{sequence_id}], PRE_AGG)
If a sequence contains multiple locations, you would receive a rank value for each location ascending by alphabetical order. Now, what you can do is implement a filter on the specific visuals where you require this value to return 1 and exclude NULLs. That way you will only return a single row per sequence ID. Let me know if you have any further questions!
Many thanks, Dylan! I had tried denseRank() and rank() but I was getting VISUAL_CALC_REFERENCE_MISSING errors. Your idea of using PRE_AGG stops the errors so I am hopeful that it is the solution for all the types of visuals that I will be working with
Hello @PaulPowerAtTeckro, PRE_AGG and PRE_FILTER calculation levels are great for work-arounds like this. They treat the field as if it isn’t aggregated so it helps avoid all of the aggregation errors like requiring all utilized fields to be included or not being able to use them with unaggregated, standard fields from your dataset. Glad I was able to help!