2 Replies Latest reply: Aug 31, 2016 9:11 AM by Luiz DePaula RSS

    Duplicate data - Calendar day week

    Luiz DePaula

      Hello experts,

       

      I am facing a challenge that it seems I cannot solve it. I am combining two data sets that have different time dimensions, one is in weeks, which includes sales forecast, and the other is in days, which includes sales. I am combining the two data sets based on a few dimensions, such as Material ID and Plant, and date. I am converting the week into a date as well.

       

      Because some weeks are split into two months, the week numbers are listed twice, one for each month it belongs to. This is causing the sales numbers to duplicate. Find example below.

       

      I am using the peek function specifying that if Calendar Week/Year is the same in the next row but the Calendar Month/Year is different, then Sales equal zero.

       

      if("%Calendar Year/Week"=peek("%Calendar Year/Week")and "%Calendar Year/Month"<>peek("%Calendar Year/Month"),0,

              "Actual Gross Cases") as Sales_peek

       

      This is causing the duplicates to be 0 for both rows, not just the one that is duplicate. I still the first value to show.

      Any suggestions?

       

      Thanks in advance,

       

      LD