Show Top 10 in Chart (Rank in Beast Mode)

b_rad

Hi,

I am trying to build a multiline chart based on the following

Dimension: Date (x-axis), Search query (line series)

Metrics: impressions and position

The multiline chart should show the position (y-axis) by date for only the top 10 search queries based on the total impressions during a the selected time period.

I cannot do the rank in the ETL because the top 10 search queries could change based on the time period selected.

Is there a way to do a beast mode calculation that would achieve this?

Thanks.

MarkSnodgrass

Looking around in the Dojo, this has been a feature that has been requested in the past, but has not been implemented yet. It may be worth submitting a new idea in the ideas exchange section of the Dojo and see if it can get included.

There is a feature called Window Functions in Beast Mode that you can ask your CSM to enable that can allow for rank and window functions to be written. However, it is still not going to get you quite there. I was able to write the following in a beast to produce a rank:

RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC)

However, when I tried to wrap it in a CASE statement so that I could drop it in a filter and exclude records with a rank higher than 10, I received a message that aggregated filters are not supported. The CASE statement looked like this.

CASE WHEN 
RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC) <= 10 THEN 'Include'
ELSE 'Exclude'
END

Since you stated that you can't do this in the ETL because you need to allow for different time periods to be used, you might need to consider a different way to visualize the data. A scatter plot card can hold a lot of series fairly effectively.

GrantSmith

Hi @b_rad

There is an Alpha feature where you can filter on aggregate functions within Domo but it doesn't always function as expected. You can talk with your CSM about possibly enabling the Aggregate Filtering feature but it's still in an Alpha state so proceed with care.

b_rad

@GrantSmith ,

Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

MarkSnodgrass

Looking around in the Dojo, this has been a feature that has been requested in the past, but has not been implemented yet. It may be worth submitting a new idea in the ideas exchange section of the Dojo and see if it can get included.

There is a feature called Window Functions in Beast Mode that you can ask your CSM to enable that can allow for rank and window functions to be written. However, it is still not going to get you quite there. I was able to write the following in a beast to produce a rank:

RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC)

However, when I tried to wrap it in a CASE statement so that I could drop it in a filter and exclude records with a rank higher than 10, I received a message that aggregated filters are not supported. The CASE statement looked like this.

CASE WHEN 
RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC) <= 10 THEN 'Include'
ELSE 'Exclude'
END

Since you stated that you can't do this in the ETL because you need to allow for different time periods to be used, you might need to consider a different way to visualize the data. A scatter plot card can hold a lot of series fairly effectively.

b_rad

Mark,

Thanks a lot for the detailed answer. This is very helpful.

Its a shame that a powerful BI tool like Domo doesn't have this feature even when a basic reporting tool like Data Studio has this functionality. I'll get the "Window function" enabled and also submit a new feature request through my CSM for this.

Thanks once again!!

GrantSmith

Hi @b_rad

There is an Alpha feature where you can filter on aggregate functions within Domo but it doesn't always function as expected. You can talk with your CSM about possibly enabling the Aggregate Filtering feature but it's still in an Alpha state so proceed with care.

b_rad

@GrantSmith ,

Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

b_rad

@GrantSmith ,

Thanks a lot for letting me know about this alpha feature. Will try to get it enabled and see if it performs as intended.

user084115

I would suggest to use Sort feature. Sort on Descending . Then using limit rows input 10. That should work

jaeW_at_Onyx

@user084060 keep in mind that the user has dates on the axis. So if my GROUP BY clause is Date and Search Query, then the number of rows I want to keep is Number of Dates x 10.

Course ... if your number of dates fluctuates you get weird results.

I know i'm not offering solutions ... I would say Domo doesn't have a good solution for this use case.

I would preaggregate in ETL or a Dataset View (and grit my teeth angrily :P )

b_rad

@user084115 your recommendation wont work because of two things

1) I want to rank on impressions but my chart is going to show a different metric "position".

2) The ranking should be for the "query" dimension but date would be on the x-axis.

I also reach out to Domo support and they actually echoed @MarkSnodgrass and @GrantSmith responses.

I got the window function alpha feature enabled and now able to rank the queries using Beast Mode calculation in the chart. I am still waiting for them to enable another alpha feature which would enable me to filter the chart based on the aggregated rank Bease Mode calculation.

Will update this thread once I get this working.

Thanks a lot everyone for your responses!!! It was very very helpful.

Jones01

Sorry picking up this old thread. Does anyone know if this is possible now.

Line chart with dates on the x axis but I want to only show the top n (from a variable) based on the total period?

I still don't think you can get the rank based on the whole range as it is grouped by date?