Creating a beastmode that filters

ColinHaze
ColinHaze Member
in Cards, Dashboards, Stories

Hi,

I am having a hard time with this simple task.

I have a column in my dataset called Lane, which is formatted as followed 'PointA>PointB.'

I am trying to create a card on a dashboard that if PointB is selected it will filter all lanes that either start with PointB or end with PointB (exmple 'PointA>PointB' would show up and 'PointB-PointC' would show up.)

Ive tried creating a beastmode which is called CitySelector which its just the shipperCity column. I then tried to create this beastmode but its only getting lanes that start with whats selected in the CitySelecor Filter. Any Ideas why?

CASE WHEN Lane LIKE CONCAT('%', CitySelector, '%') THEN 'Y' ELSE 'N' END

Thanks for the help!

Best Answers

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @ColinHaze,

    How about using variables for this with a search field? Would it be a viable solution? Here's an example.

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Short answer, no.

    Manasi suggested using text-input as the control, which is probably the easiest way for this use case.

    For a drop-down, once you laboriously populate the variable, it can be a pain to make sure the values you have populated for the variable on your card are the same as the ones you have in your control, as each instance of a control for a variable can have a unique list, and it does not stay synced with any other list. So if your city list changed at all, it would not be dynamic, and even manually updating it can be buggy.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @ColinHaze,


    Create a Search Variable like this:

    image.png

    Create a CASE statement in Beast mode like this:
    CASE WHEN `Search Filter` = '' THEN 'TRUE' ELSE (CASE WHEN `Lane` LIKE CONCAT('%',`Search Filter`,'%') THEN 'TRUE' ELSE 'FALSE' END) END

    Add Beast mode formula to ‘Filter’ with the following settings:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • DavidChurchman
    DavidChurchman Coach

    It appears logically sound. Can you give some examples of city/lane combinations that worked in your filter and ones that did not work?

    I suspect it's some text formatting thing. Wrapping your variables in UPPER() and TRIM() might be good, to make sure it's not a case/white space issue. If that doesn't do it, I'd need to see when it's working/not working to be able to de-bug.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Manasi_Panov
    Manasi_Panov Contributor

    Hi @ColinHaze,

    Could you please show the values in 'shipperCity'?

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • ColinHaze
    ColinHaze Member
    edited December 18

    Manasi_Panov

    Here is some sample Data. CitySelector is a beastmode that is the shipper_city_State Field.

    image.png


    DavidChurchman

    When I select a certain city off the cityselector, only those with matching shipper_city_states appear on the card, none with matching consignee_city_states appear.

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @ColinHaze,

    The issue likely arises because 'Shipper_City_State' and 'CitySelector' have identical values, causing the 'CitySelector' filter to also apply to this column and exclude all rows where 'Consignee_City_State' matches 'CitySelector'.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @ColinHaze,

    How about using variables for this with a search field? Would it be a viable solution? Here's an example.

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • DavidChurchman
    DavidChurchman Coach
    image.png

    I see the problem with your approach. Your filter is looking for rows where the CitySelector is in the Lane, but because the CitySelector is only the Shipper_City_State, there are no rows that exist where the CitySelector is the same as the Consignee.

    For this approach to work, you'd need to double the length of your dataset, and have one lane where the CitySelector is the Shipper and a duplicate lane where the CitySelector is the Consignee.

    Alternatively, @Manasi_Panov 's variable approach is sound, though you would rely on people being able to type out city names.

    This is also a use case where being able to combine filters with an "OR" would be really helpful, which has been suggested a handful of times on the ideas exchange, although I'm sure a few more times wouldn't hurt.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ColinHaze
    ColinHaze Member

    Manasi_Panov & DavidChurchman
    Is there an easy way to populate the variables without having to manually enter all the citys in?

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Short answer, no.

    Manasi suggested using text-input as the control, which is probably the easiest way for this use case.

    For a drop-down, once you laboriously populate the variable, it can be a pain to make sure the values you have populated for the variable on your card are the same as the ones you have in your control, as each instance of a control for a variable can have a unique list, and it does not stay synced with any other list. So if your city list changed at all, it would not be dynamic, and even manually updating it can be buggy.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ColinHaze
    ColinHaze Member

    Manasi_Panov & DavidChurchman

    Thank you both! Im gonna try out this text variable and see how it goes.

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @ColinHaze,


    Create a Search Variable like this:

    image.png

    Create a CASE statement in Beast mode like this:
    CASE WHEN `Search Filter` = '' THEN 'TRUE' ELSE (CASE WHEN `Lane` LIKE CONCAT('%',`Search Filter`,'%') THEN 'TRUE' ELSE 'FALSE' END) END

    Add Beast mode formula to ‘Filter’ with the following settings:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories