Comparative gauge and filters?

mberkeley
mberkeley βšͺ️

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 NY 🟠

    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

  • GrantSmith
    GrantSmith Indiana 🟀

    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)

  • mberkeley
    mberkeley βšͺ️

    @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 βšͺ️

    @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 NY 🟠

    @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

Sign In or Register to comment.