sum if not working

[#1]

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?

[sebastiandperei]

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;

[jolivares]

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

Good luck!!

[#4]

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

[sebastiandperei]

Was it useful for you?