Set Conditional Formatting Based on a Separate Measure

Comments

7 comments

  • Avatar
    Joseph Peters

    Could we add this kind of conditional formatting to the data bars in a pivot table?  For instance green if + and red if -?

     

    0
    Comment actions Permalink
  • Avatar
    Dot Q

    I tried this on a pivot table and got no results. Any ideas?

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Hi All,

    This can actually be easily achieved without the need for JS. This solution will work for both charts and pivots, I guess for any other widget as well.

    Shout out to Itay Seroussi for this :) Thank you Eti

    He describes it well in this article

    Basically, within the conditional formatting formula, we will need to create a situation where the KPI upon which we want to apply the conditional formatting, on, which we will call the independent measure, will be cancelled out in the calculation.

    So in the formula editor of the independent measure we will begin with multiplying the formula by the independent measure, then the threshold we would like to apply to the dependant measure, and then divide by the dependant measure, or something like this:

    < (Independent Measure)*(Threshold)/(Dependant Measure)   -> RED

    >(Independent Measure)*(Threshold)/(Dependant Measure)    -> GREEN

    Let me know if you'd like any further clarification on this, feel free to reach out 

    Ido Darnell

    ido.darnell@qbeeq.pl

    www.qbeeq.pl

    2019 SISENSE PARTNER OF THE YEAR

    0
    Comment actions Permalink
  • Avatar
    Dot Q

    Hi Ido, 

    That link does not work and unfortunately, the method you are describing is what I attempted to do before attempting the JS described in this post.

    While the logic is sound, I continually get Elasticube errors that are vague (e.g. Unknown error occurred or a memory consumption error). I've tried to save formulas, write formulas directly in the condition, and tried it on various aggregates and I keep getting the same Elasticube errors.

    Given that Sisense is so sensitive to Elasticube errors, I am more inclined to use JS.

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Hi,

    I previously placed an incomplete link, here you go:

    https://support.sisense.com/hc/en-us/articles/230648308-Applying-Conditional-Formatting-Based-on-Another-Measure-Performance-

    That logic is working for me on both pivot tables and column/bar charts. The issue you're describing sounds like the formula is a bit too robust for the formula editor. Something that works may times for me when creating complex formulas is to create them step by step and starring them as staging formulas, then create the full formula by combining the staging formulas according to your required calculation. Then star the end formula as well.

    Try to keep the formula as bear as possible, I would try that and test this logic again.

    In any case you're more than welcome to reach out for a free consultation,

    Ido

    0
    Comment actions Permalink
  • Avatar
    Dot Q

    Hi Ido,

    Thank you for sharing the link. Incidentally, this is the exact link I followed initially. My formula is the same number of steps as the one you mentioned and even less robust than the example in the link (as there are no nested formulas). Additionally, I have tried breaking it into multiple parts, both starring the formula and explicitly writing the formula into the condition. Even when I created a starred formula and included in my widget (before pulling it into the condition), I was getting the "unknown error". 

    The most unfortunate part is that the error message is so vague so it could be a number of things, including a bug.

     

     

    0
    Comment actions Permalink

Please sign in to leave a comment.