1 Reply Latest reply on Dec 16, 2016 10:06 AM by Simon Runc

    How to filter non-agg data within calculation

    Phillip Typaldos

      All,

       

      I have a calculated field that is dependent on a filter to calculate the correct information. The "performance" calculated field in the attached viz will only display the correct results if the "Downtime code" is filtered to Null (could also be downtime code description). The issue I am having is that I have a calculated "OEE" is a calculated of three calculated fields "Availability"x"Performance"x"Quality" and I need to somehow only filter out "downtime code" as null for only the "performance" part of the calculation. When I try to do that using an IF statement I get the cannot mix aggregate and non-aggregate error. I tried to also use IFNull to assign a different name to Null and then the IF statement but it does not work. I think LOD is the right way to go but it doesnt seem like something I can use for filtering specific calculated fields that are combined in a calculation?

       

      See attached viz

        • 1. Re: How to filter non-agg data within calculation
          Simon Runc

          hi Phillip,

           

          I'm not sure I understand your calculation exactly...but you can embedd a Row Level filter into the calculation. With the caveat of my limited understanding!...the below is an example that will only return values for the SUMs when the [downtime code] in not NULL

           

          SUM(IIF(NOT(ISNULL([Downtime_Code])),[Outputs],0))/

          SUM(IIF(NOT(ISNULL([Downtime_Code])),[Max Throughput per Hour]*([Duration Minutes]/60),0))

           

          Hopefully, this makes sense, and you can apply the general idea to your situation.