Reuse Custom Metrics in DQL Without Reinitialisation

[knallapa]

I'm currently using this query to reuse the same custom metric at different time intervals to populate table data in my dashboard. However, I’d like to simplify the query and reuse a variable multiple times with different conditions in DQL. Is there a better way to structure this?

timeseries METRIC_A_approved = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeA" AND
(responseCode == "0" OR responseCode == "00") AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }

| join [
timeseries METRIC_A_total = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeA" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_A_total }, kind: outer

| join [
timeseries METRIC_B_approved = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved }, kind: outer

| join [
timeseries METRIC_B_total = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total }, kind: outer

| join [
timeseries METRIC_B_approved_subgroup = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
(categoryCode in ["1001", "1002", "1003", "1004", "1005"]) AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_subgroup }, kind: outer

| join [
timeseries METRIC_B_total_subgroup = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(categoryCode in ["1001", "1002", "1003", "1004", "1005"]) AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_subgroup }, kind: outer

| join [
timeseries METRIC_B_approved_domestic = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
categoryCode == "2001" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_domestic }, kind: outer

| join [
timeseries METRIC_B_total_domestic = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
categoryCode == "2001" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_domestic }, kind: outer

| join [
timeseries METRIC_B_approved_crossborder = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
categoryCode == "2002" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_crossborder }, kind: outer

| join [
timeseries METRIC_B_total_crossborder = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
categoryCode == "2002" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_crossborder }, kind: outer

| fieldsAdd
Rate_A = if(coalesce(METRIC_A_total, 0) == 0, 0, 100 * coalesce(METRIC_A_approved, 0) / coalesce(METRIC_A_total, 0)),
Rate_B = if(coalesce(METRIC_B_total, 0) == 0, 0, 100 * coalesce(METRIC_B_approved, 0) / coalesce(METRIC_B_total, 0)),
Rate_B_subgroup = if(coalesce(METRIC_B_total_subgroup, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_subgroup, 0) / coalesce(METRIC_B_total_subgroup, 0)),
Rate_B_domestic = if(coalesce(METRIC_B_total_domestic, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_domestic, 0) / coalesce(METRIC_B_total_domestic, 0)),
Rate_B_crossborder = if(coalesce(METRIC_B_total_crossborder, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_crossborder, 0) / coalesce(METRIC_B_total_crossborder, 0))

| filter not(isNull(entity) or entity == "")
| fieldsAdd
Entity = entity,
A.Total = toLong(coalesce(METRIC_A_total, 0)),
A.Rate = concat(toString(round(coalesce(Rate_A, 0), decimals: 2)), "%"),
B.Total = toLong(coalesce(METRIC_B_total, 0)),
B.Rate = concat(toString(round(coalesce(Rate_B, 0), decimals: 2)), "%"),
B.Total.Sub = toLong(coalesce(METRIC_B_total_subgroup, 0)),
B.Rate.Sub = concat(toString(round(coalesce(Rate_B_subgroup, 0), decimals: 2)), "%"),
B.Total.Dom = toLong(coalesce(METRIC_B_total_domestic, 0)),
B.Rate.Dom = concat(toString(round(coalesce(Rate_B_domestic, 0), decimals: 2)), "%"),
B.Total.XB = toLong(coalesce(METRIC_B_total_crossborder, 0)),
B.Rate.XB = concat(toString(round(coalesce(Rate_B_crossborder, 0), decimals: 2)), "%")

| fields Entity, A.Total, A.Rate, B.Total, B.Rate, B.Total.Dom, B.Rate.Dom, B.Total.XB, B.Rate.XB, B.Total.Sub, B.Rate.Sub
| sort A.Rate desc

[Maheedhar_T · Accepted solution]

Hi @knallapa ,
You can set up the common thing in all queries as variable and reference it in dashboard tiles.
Reference: Add a variable to a dashboard — Dynatrace Docs
You can add as many variables as you want to. Maybe set up one variable for totalTransactions then use that in calculations. Set up a constant timeframe for that variable and let the other tiles have dashboard timeframe.

Regards,
@Maheedhar_T 

[knallapa]

Sure, I will try in that direction. Thankyou @Maheedhar_T 

[knallapa]

For Above Query Nothing worked for me: Tried variables, Problems i faced is : 

• can’t fetch a metric once and reuse it with conditional logic applying filters.
• can’t assign a timeseries result to a variable inside fetch.
• can’t use let to define reusable filters inside fetch.

Moreover variables have limits. other issue was variables i can't extend and apply filter conditions to that variable.