Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

4 Replies
sebastiandperei
Specialist
Specialist

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
Specialist
Specialist

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

Good luck!!

Not applicable
Author

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
Specialist
Specialist

Was it useful for you?