
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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
;
- Tags:
- sum

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
After the Load statment, so it would be:
Load distinct [Field1] , [Field2], etc... Only in your first table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, but still doesn't work
