Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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.
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?
After the Load statment, so it would be:
Load distinct [Field1] , [Field2], etc... Only in your first table
Thanks, but still doesn't work