4 Replies Latest reply: Mar 13, 2012 2:44 PM by Sebastian Pereira RSS

    sum if not working

      I created a chart that displays data grouped into weeks.  The dimension is a temp calendar which shows all week starts for the next 7 weeks.  I created the temp calendar so it would show weeks with no data.

       

      See my qvw doc attached.  My expression for sum by week is: sum(if(TempMRPWeek=MRPWeek,MRPQty,0))

       

      When summing the data my sums seem to be extremely high.  The total for week 3/5/2012 should be 130 but the chart is showing 910.  Any idea why the sum is not working correctly?  What other way should I try to sum?

        • sum if not working
          Sebastian Pereira

          Hi Chris...

           

          Your TMP calendar repeats 7 times every date, and it causes that "sum" function add each value 7 times.

           

          Easier solution, is to put Distinct in your expression:

           

          Sum(Distinct if(TempMRPWeek=MRPWeek,MRPQty,0))

           

          But, if you have two same values, it will take only one. Another way is, later of the script, read the Temp Calendar again, then drop the first:

           

          ...

          //Calendar

          LET vDateMin = Num(WeekStart(Today()-7));

          LET NoOfDays = 546;

          TempCalendar:

          LOAD

          weekstart(date($(vDateMin) + recno() + 6)) as TempMRPWeek

          AUTOGENERATE $(NoOfDays);

           

          TempCal:

          Load distinct * Resident TempCalendar;

           

          Drop Table TempCalendar;

          • Re: sum if not working
            Juan Olivares

            See the model attach... to show weeks with no data, expand table part to this weeks joinning tables.

             

            Good luck!!

            • sum if not working
              Brandon Apperson

              Based on what you've said  910 is 7 times as much as 130.  There are duplicate entries forming for the same data(probably one for each day of the week).

               

              But the way to solve this is adding distinct in the formula:

               

              =sum(DISTINCT if(TempMRPWeek=MRPWeek ,MRPQty,0))

               

              This gives me the results I think you are looking for.

               

              Hope this helps,

              Brandon