Global Performance vs. Store Performance in a KPI Visual

Question & Answer
, ,
1

I am creating a KPI visual that shows specific store performance for the last month vs. global average store performance last month.

  • Store is being selected through a parameter.
  • My performance metric is an aggregated field.
  • Data is structured around individual delivery records with a store id, so performance metrics need to remain aggregated.

I can’t apply the Store filter to the visual without breaking my target performance metric obviously, and I have tried several ways of creating calculated fields that are pre filtering by the site selected in the parameter, examples of what has NOT worked thus far:

  • ifelse({Store} = ${Store}, sum({Pounds Per Delivery}), 0)
  • ifelse(max({Store}) = ${Store}, {Pounds Per Delivery}, 0)
  • ifelse({store_id} = ${SelectedStoreParameter},
    sum({total_pounds}) / sum({delivery_count}), 0)

Most of these attempts resulted in mismatched aggregation, aggregating Store with min and max has not worked because it’s a text field, I believe.

I need the target field to be the average Pounds per Delivery, aggregated on the store level, and then the primary value will be the pounds per delivery for the selected store. Seems like it should be simple but I’ve sunk a morning into it at this point haha any insights would be much appreciated!

2

Hi @joeFRUS,

I’m not sure I understand your data enough to check if your calculated fields will give you the correct result but I can help with your mismatched aggregation error.

Instead of using ifelse, replace the sum by sumIf.

sumIf({total_pounds}, {store_id} = ${SelectedStoreParameter})/sumIf({delivery_count}, {store_id} = ${SelectedStoreParameter})

sumIf({Pounds Per Delivery}, {Store} = ${Store})

Then, if you want to display 0 instead of null, use coalesce.

coalesce(sumIf({total_pounds}, {store_id} = ${SelectedStoreParameter})/sumIf({delivery_count}, {store_id} = ${SelectedStoreParameter}), 0)

coalesce(sumIf({Pounds Per Delivery}, {Store} = ${Store}), 0)

If this fixes your mismatched aggregation error but doesn’t give you the correct result, can you share a few rows of sample data?

1 Like
3

Hey @David_Wong thank you for the response, I mocked up an example in Arena: Example

sumIf({Pounds Per Delivery}, {Store} = ${Store}) - This ran me into the nested aggregation error:

Nesting of aggregate functions like SUM and SUM(CASE WHEN “Site” = _UTF16’Fairfield County, CT’ THEN SUM(“calculated_lbs”) / NULLIF(COUNT(DISTINCT “rescue_id”), 0) ELSE NULL END) is not allowed.

sumIf({total_pounds}, {store_id} = ${SelectedStoreParameter})/sumIf({delivery_count}, {store_id} = ${SelectedStoreParameter}) - This ran also me into the nested aggregation error:

Nesting of aggregate functions like / and SUM(CASE WHEN “Site” = _UTF16’Fairfield County, CT’ THEN “Weight (lbs)” ELSE NULL END) / NULLIF(SUM(CASE WHEN “Site” = _UTF16’Fairfield County, CT’ THEN COUNT(DISTINCT “rescue_id”) ELSE NULL END), 0) is not allowed.

4

Hi @joeFRUS,

The Arena link that you shared doesn’t work. Can you please try sharing it again?

The error messages that you shared help to clarify that {Pounds Per Delivery} is a calculated field which makes a big difference in the calculation. We can’t put a sum in a sumIf. That’s why it’s giving that nested aggregation error. Is calculated_lbs also a calculated field?

Can you try this?

coalesce(sumIf({Weight (lbs)}, {Store} = ${Store}) / distinct_countIf({rescue_id}, {Store} = ${Store}), 0)
2 Likes
5

That worked! Thank you so much @David_Wong

1 Like