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

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)

CodePudding user response：

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_*

(Only a sample of the output)