Forum Discussion

szimmermann's avatar
szimmermann
Cloud Apps
11-17-2021

Unit Conversions as a Dashboard Filter

Hey, there!

I have several business requirements for multiple dashboards to include a "toggle" of sorts between different unit conversions -- most often it's currency, temperature, and ft2/m2, and with the conversion calcs in their own dim tables. So what we've been doing is creating a dashboard level filter based on the unit description and make it single select. Then we use a formula with this kind of syntax where max is the conversion rate, otherwise it is 0: 

SUM([BUILDING_ID], ([Total COST_EUR]* [Max EUR]+ [Total COST_GBP]* [Max GBP]+ [Total COST_USD]* [Max USD]))

This works, but there is room for error in that the user can turn the filters on and off and drastically skew the results that way. Is there a way to lock down the ability to turn filters on/off? 

Additionally, I'd be interested to hear some other ways to handle this type of requirement. Thanks!

dashboards & reporting

8 Replies

Replies have been turned off for this discussion
  • hlorosc's avatar
    hlorosc
    Cloud Apps
    11-17-2021

    Ahh realizing you want them to change the filter but NOT exclude it all together - apologies for reading to fast! That I don't know... if I figure it out I'll share!

    • szimmermann's avatar
      szimmermann
      Cloud Apps
      11-18-2021

      Thanks!! Switchable Dimensions looks like a good option. I only just recently discovered the lock feature on the filters, and even more recently the power of the background filter, so awesome!! 

      I just saw a community blog post from last night referencing currency conversions ... I'm trying to work through the YouTube video now to see if I can get it to work for my use cases, thought I'd put it here too for anyone else. 

      Currency Conversion Switch Tutorial - Sisense BloX with Diego Cordero - YouTube

  • hlorosc's avatar
    hlorosc
    Cloud Apps
    11-17-2021

    You can definitely lock a filter at the dashboard level (I'm doing this with some important ones so people know they're there but don't mess with them).  

    On the filter box, click the three dots in the upper right corner and then select "lock". That keeps the dashboard filter from being changed by viewers.

    I'm starting to think about the toggling results option as well, and plan to look into the Switchable Dimensions add-on once I get a little more experienced in the basics. https://www.sisense.com/marketplace/switchable-dimensions/

    Requires editing script a bit - maybe someone here has used it before?

  • irismaessen's avatar
    irismaessen
    Data Pipeline
    11-18-2021

    I had the vague idea that this *must* have been handled before, and I found this post from the old forums that Sisense Support has been kind enough to preserve as documentation. I believe it shows  how you could use a conversion factor as a multiplier and then set the dashboard filter to one of the conversions. If I understand it correctly (I haven't had time to experiment), if the user disabled the filter it would set the values to 0 or maybe even N/a.

    • szimmermann's avatar
      szimmermann
      Cloud Apps
      11-18-2021

      Thanks for sharing that link -- bookmarked!! It explains way better than I could what my dashboard and model are currently doing. I received feedback from a client recently that showed that when the filter is disabled (they did not realize it had been disabled), the calc conversion values are all added together. I had hoped the result would have been N/A, but I guess it makes sense as the formula is written.

      I'm looking for a way to lock the filter so that it is always turned on and the user cannot disable it, forcing a filter selection. I guess the title of my original post is a little misleading now that I think about it -- sorry about that.

      • irismaessen's avatar
        irismaessen
        Data Pipeline
        11-19-2021

        I think the example does things slightly differently from the approach you showed in your post. You sum all the converted values. The article instead defines the conversion factor as a single multiplier. It uses the average, but you could also use Min or Max to get a default value. When the filter is disabled in this case, the multiplier is still a single value

        So something like SUM([BUILDING], [TotalCost])*MAX(Conversionfactor)

        I guess for that also you should also only have a single costs column, and not a costEUR, costGBP, and costUSD columns as you now appear to have

  • szimmermann's avatar
    szimmermann
    Cloud Apps
    11-22-2021

    Oh, you're right, it is a different approach and could solve the issue. I didn't look at that closely enough. Also hadn't heard of the community filter widget ... looks promising! Thank you so much!!