Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
nezuko_kamado
Contributor III
Contributor III

Aggregating a new table with Sum() and Group by in load editor (SUM is not working properly)

Initial data set is huge, so I subset and  create 'original'  set. Then aggregate Sum of daily unit by ID  and calendarDate.

However, Summation is not correct by calendarDate for IDs.

For example, the screen capture shows  days of units for ONE specific ID.

11/2/2020 summation for the ID has to be 7 not 12.  So as 11/3/2020 and 11/4/2020. I don't know where that random summation came from after grouped.

image001.png

Is there any error from my code in aggregation? I tried, no concat, only() instead of sum() and so on... none of them worked

original:
Load
ID,
calendarDate,
HourUnit as Unit
Resident fullData;

Grouped:
LOAD
ID,
calendarDate,
Sum(Unit) as Unit2
resident original
Group by ID, calendarDate
;

 

 

Labels (2)
4 Replies
Gui_Approbato
Creator III
Creator III

You have duplicates in your dataset.

Notice that you are using the values as dimensions. Try to add in your chart the column with the Measure SUM(Unit).

Or use the DISTINCT to remove the duplicates.

nezuko_kamado
Contributor III
Contributor III
Author

Because other calculation applies to the sum result, I don't create this as measure but a table in load editor.

If I want to use Distinct, where should I input this?

 

Gui_Approbato
Creator III
Creator III

After the Load statment, so it would be:

Load distinct [Field1] , [Field2], etc... Only in your first table

nezuko_kamado
Contributor III
Contributor III
Author

Thanks, but still doesn't work