Comparative gauge and filters?

mberkeley
mberkeley Contributor

I am trying to build a gauge that shows a % for the currently selected company value in comparison to the % for the community as a whole.

The problem i run into is that as soon as I filter the card to the current company, I no longer have the entire community's data to compare against?

I don't see any way to just filter for 1 element of the data, but not the other.

How have others solved this problem?

thanks in advance.

Best Answer

Answers

  • rahul93
    rahul93 Contributor

    Hi,

    One way you can solve this problem is by creating a beast mode. So let's say you want company A & community values. You can say something like:

    case when "Filter Column" in ('Company A', 'community') then 'Company A'
     when "Filter Column" in ('Company B', 'community') then 'Company B'
    end
    

    Basically, you can't filter 1 element of the dataset because filtering on 1 element of the dataset will affect the entire dataset (This is true for any relational database/ structure).


    Let me know if I was not clear or I got anything wrong.


    Thanks

  • Hi @mberkeley

    You'd need to pre-calculate the total in another DataFlow or dataset view such that you have column value for the population total to compare against when filtering records.

    I'd recommend using a DataSet View (in beta) and then use a window function to get the entire total for the dataset and put that value in each row.

    Alternatively you can utilize a group by tile in a magic ETL DataFlow (since you need a field to group on you'll need to add a constant to your dataset in a new column - call it 'grouping' - with a value of 1 and then use that in your group by tile)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @mberkeley pretty sure you can get the result without pre-aggregating your data.

    @GrantSmith and I both proposed solutions that should work here:

    https://dojo.domo.com/discussion/52390/tableau-like-parameter-actions#latest

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • mberkeley
    mberkeley Contributor

    @rahul93

    Your solution would work if I always wanted to compare Company A to the community, however, I need to be able to compare any company against the community when filtered from a story dashboard.

  • mberkeley
    mberkeley Contributor

    @GrantSmith

    I am trying to get access to the DataSet View beta now. Hopefully, that will allow me to do this effectively. thanks!

  • rahul93
    rahul93 Contributor

    @mberkeley


    This solution would work for all companies, the only issue is that you would have put each company in a case statement. The filter will look like

    'Company A'

    'Company B' and so on and once you click on any one of them it will compare that company or companies (based on your selection) to the community.

    I have mentioned the case statement in my earlier comment. You also do not need to pre-total it before you create the dataset. I am doing this in various cards for my organization.

    Thanks