I'm trying to produce a hierarchical aggregation by using top-nested Kusto operator for export to xlsx and works well but i'm loosing my default order by "Class" and "Date" fields by using query:
Test | top-nested 25 of Class by sum(Value), top-nested 25 of Date by sum(Value)
my data in db xlsx file
expected result on this image (same as above but Order by Class) query:
Test | where (tolower(Class) == tolower('Väganläggning'))| summarize sum(Value) by Date | take 25
but i'm getting data with strange order for "Date" and "Class" like on this exported xlsx file (first "Date" 2029 year instead 2009 and so on)
Seems i need query like this
Test | where Class == prev(Class) | top-nested 25 of Class by sum(Value), top-nested 25 of Date by sum(Value)
but i'm not experienced in Kusto, so need help guys)
The last dimension column goes to the Values
section in the Excel Pivot.
The rest of the columns goes to the Rows
section.
// Generate data sample. Not part of the solution.
let Test = materialize
(
range i from 1 to 1000000 step 1
| extend Class = strcat("Class-", tostring(toint(rand(10))))
,Date = startofday(ago(1d*rand(30)))
,Value = rand()
,Dim1 = strcat("Dim1-", tostring(toint(rand(10))))
,Dim2 = strcat("Dim2-", tostring(toint(rand(10))))
,Dim3 = strcat("Dim3-", tostring(toint(rand(10))))
);
// Solution starts here.
Test
| top-nested 4 of Class by sum(Value)
,top-nested 3 of Date by sum(Value)
,top-nested 2 of Dim1 by sum(Value)
,top-nested 2 of Dim2 by sum(Value)
,top-nested 2 of Dim3 by sum(Value)
| order by aggregated_Class ,Class asc
,aggregated_Date ,Date asc
,aggregated_Dim1 ,Dim1 asc
,aggregated_Dim2 ,Dim2 asc
,aggregated_Dim3 ,Dim3 asc
| project-rename Values = aggregated_Dim3
| project-away aggregated_*
Class | Date | Dim1 | Dim2 | Dim3 | Values |
---|---|---|---|---|---|
Class-1 | 2022-05-10T00:00:00Z | Dim1-2 | Dim2-8 | Dim3-8 | 5.1418120760105817 |
Class-1 | 2022-05-10T00:00:00Z | Dim1-2 | Dim2-8 | Dim3-4 | 4.8139672995169027 |
Class-1 | 2022-05-10T00:00:00Z | Dim1-2 | Dim2-5 | Dim3-4 | 3.6333999871855704 |
Class-1 | 2022-05-10T00:00:00Z | Dim1-2 | Dim2-5 | Dim3-0 | 3.5165481405530339 |
Class-1 | 2022-05-10T00:00:00Z | Dim1-6 | Dim2-9 | Dim3-3 | 3.526092609498761 |
(Only a sample of the output)