Save order when using top-nested Kusto operator

Question

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)

user_6336479 · Answer

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)

Fiddle

Save order when using top-nested Kusto operator

1 Answers