2 Replies Latest reply: Sep 15, 2011 3:04 PM by Jason Michaelides RSS

    Methodology questions (nested if vs. set analysis) + Avg Reference Line

    Michael Ellerbeck

      Sorry this is a couple questions in one. But the first that I have been wondering lately is say I have

       

      LOT      QTY     WEIGHT     TRAN_DATE     WHS

      a1          1000     1               1/1/2011          A

      b1          1000     1               1/1/2011          B

       

      And I want to create a chart that only shows the QTY * Weight for the last 7 days for WHS A I typically do a somewhat crazy nested if like this

       

      sum(if(TRAN_DATE > vNewDate - 7,

      if(WHS='A',QTY * WEIGHT)))

       

      It feels like I should maybe be using set Analysis for this sort of thing? Any thoughts, is there a better/different/easier way?

       

      ----

       

      I am also trying to figure out how to do an Average Reference Line. Lets say

      LOT
      QTYWEIGHTTRAN_DATEWHS
      a1100011/1/2011A
      a1-100011/1/2011A
      b1100011/2/2011A
      c1100011/3/2011A
      c2100011/3/2011

       

      The correct average should be

       

      3000 / 3 or 1,000  (don't count 1/1/2011 because a net total of zero was produced )     

       

      So to get the average I think I would need to sum the QTY for the day, and then divide by the days that don't have a zero.

       

      I started out trying the below but of course it doesn't work because it still would count the day that has zero..

       

      =Count (DISTINCT if(TRAN_DATE> vNewDate -7 AND TRAN_DATE <=vNewDate,

      if(WHS='A', TRAN_DATE

      )))

       

      Thanks for any comments!