Search code examples
azure-data-explorerkql

Calculate percentage of values ​based on different dates


Want to get this [1]: https://i.sstatic.net/8dXSG.png so i have 2018 as index period and need calculate difference in each year with 2018: example 2018 and 2019, 2018 and 2020, 2018 and 2021...

Need to use something like summarize total=sum(value) | where date == '2018-01-01' but how iterate each value to 2024 year?

Please help, checked Kusto doc but hav'nt find answer how to do that :(

i have this table

datatable(Value:int, Date:datetime) 
    [2121, datetime(2018-01-01), //100% as this is index perios
     5645, datetime(2019-01-01), // will be 266%
     5123, datetime(2020-01-01),
     4478, datetime(2024-01-01),
     9965, datetime(2024-01-01),
     7645, datetime(2024-01-01),
    ];

Solution

  • let t = (datatable(Value:int, Date:datetime) 
        [2121, datetime(2018-01-01), //100% as this is index perios
         5645, datetime(2019-01-01), // will be 266%
         5123, datetime(2020-01-01),
         4478, datetime(2024-01-01),
         9965, datetime(2024-01-01),
         7645, datetime(2024-01-01),
        ]);
    let val_2018 = toscalar(t | where Date == datetime(2018-01-01) | summarize sum(Value));
    t
    | summarize percent = 100.0 * sum(Value) / val_2018 by Date
    | render linechart with (xcolumn=Date, ycolumns=percent) 
    

    enter image description here

    Fiddle