topic sum if not working in QlikView

sum if not working

Fri, 09 Mar 2012 19:00:46 GMT

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

sebastiandperei — Fri, 09 Mar 2012 20:23:50 GMT

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

jolivares — Fri, 09 Mar 2012 20:26:59 GMT

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

Good luck!!

sum if not working

Fri, 09 Mar 2012 20:30:01 GMT

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

sum if not working

sebastiandperei — Tue, 13 Mar 2012 18:44:24 GMT

Was it useful for you?