How to make make-series conditional on another value?

user_16172435 0
Viewed 40

I am using a query to calculate two values but I need each series to only include records of a specific type in the calculation

let _timePeriodInHours = abs(datetime_diff('hour', _startTime, _endTime));
let _stepConfig = case(_timePeriodInHours <= 24, timespan(1h), _timePeriodInHours < 720, timespan(1d), timespan(30d));
tilldevicedata
| where todatetime(TransactionTimeStampUtc) between (_startTime.._endTime)
| where Brand has_any (_brand)
| where Country == _country
| where Store has_any (_store)
| where TransactionValueDiscount == 0
| where IsReturnTransaction == 0
| make-series AvgBasketValueNonDiscountedBaskets = round(avg(TransactionValueGross), 2), AvgBasketValueDiscountedBaskets = round(avg(TransactionValueNet), 2) on todatetime(TransactionTimeStampUtc) from bin(_startTime,_stepConfig) to _endTime step _stepConfig

I want the make-series for AvgBasketValueNonDiscountedBaskets to ONLY include rows that has TransactionValueDiscount == 0 and I want AvgBasketValueDiscountedBaskets to ONLY include rows that has TransactionValueDiscount > 0

Can this be achieved?

kql azure-data-explorer
1 Answers

avgif()

| make-series AvgBasketValueNonDiscountedBaskets = round(avgif(TransactionValueGross, TransactionValueDiscount == 0), 2), AvgBasketValueDiscountedBaskets = round(avgif(TransactionValueNet, TransactionValueDiscount > 0), 2) on todatetime(TransactionTimeStampUtc) from bin(_startTime,_stepConfig) to _endTime step _stepConfig
user_63364790
Related