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



      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!