Different X axis scale based on user parameter

user_16172435 0
Viewed 35

I am trying to make a dashboard where the user can select the X axis scale using a parameter.

So if they choose "month" then the X axis is broken down by month as per below

tilldevicedata
| where todatetime(TransactionTimeStampUtc) between (_startTime.._endTime)
| where Brand has_any (_brand)
| where Country == _country
| where Store has_any (_store)
| summarize TotalSales = sum(TransactionValueGross) by monthofyear(todatetime(TransactionTimeStampUtc))

If they choose week then I want the breakdown to be by weekofyear instead of monthofyear

How can I make that part of the query conditional based on a user parameter in the Data Explorer dashboard?

-- EDIT --

I tried to make it dynamic but I only got monthofyear, it never hits dayofyear and I dont understand why

let _timePeriodInHours = datetime_diff("Hour", _startTime, _endTime);

    tilldevicedata
    | where todatetime(TransactionTimeStampUtc) between (_startTime.._endTime)
    | where Brand has_any (_brand)
    | where Country == _country
    | where Store has_any (_store)
    | summarize TotalSales = sum(TransactionValueGross) by case(_timePeriodInHours < 1, monthofyear(todatetime(TransactionTimeStampUtc)), _timePeriodInHours > 1, dayofyear(todatetime(TransactionTimeStampUtc)), _timePeriodInHours)
kql azure-data-explorer
1 Answers

Sample query
Note the use of summarize ... by case(...)

range i from 1 to 10000 step 1    
| extend dttm = ago(1000d * rand())
| summarize count() by case(_period == 'month', startofmonth(dttm), _period == 'week', startofweek(dttm), datetime(null))
| render timechart

_period parameter definition

_period parameter definition

Week

week

Month

month

user_63364790
Related