Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Ignore average if not available

Hi Guys, I wan't to ignore an average which is calculated in a certain metric when there is no data available for a certain year for another metric. I have this matrix: Year Avg1 Avg2 ...
gvrajesh's avatar
gvrajesh
Frequent Visitor
5 years ago

Hi Anonymous 

You can calculate Avg2 only when Avg1 is not blank using below logic

Avg2 = IF( ISBLANK(AVG1), BLANK(),sum(sales) / distinctcount(orderid))

  • rfigtree's avatar
    rfigtree
    Icon for Resolver III rankResolver III
    5 years ago

    i was thinking the same, but not too sure it will work when it calculates at the total level. as when the year context is removed [avg1] wont be blank so 2017 will be included? just thinking I could be wrong.

    • gvrajesh's avatar
      gvrajesh
      Frequent Visitor
      5 years ago

      Hi rfigtree 

      Yes you are correct. To fix that we've to create virtual table like below

       

      • rfigtree's avatar
        rfigtree
        Icon for Resolver III rankResolver III
        5 years ago

        cool, it is fun seeing peoples solutions on here. always learning something new.