Hello,
I am building a dynamic QuickSight Visual where users can toggle between different views.
Here’s the setup:
-
${ShelfChangesValueParam}is a parameter that lets users choose between:- Placements
- Average $ Net Price
- Average $ Promotion
- Average % Discount
-
${ShelfChangesParam}is a parameter that lets users toggle between:- Brand (dataset field
{brand_name}) - Retailer (dataset field
{merchant_name}) - Dynamic Price Band (calculated field inside the analysis)
- Brand (dataset field
- Formula :-
ifelse(
${ShelfChangesValueParam} = “Placements”,
ifelse(
${ShelfChangesParam} = ‘Brands’,
distinctCountOver(concat({part_number},{merchant_name}), [{brand_name}, Year], PRE_AGG),
${ShelfChangesParam} = ‘Retailers’,
distinctCountOver(concat({part_number},{merchant_name}), [{merchant_name}, Year], PRE_AGG),
distinctCountOver(concat({part_number},{merchant_name}), [{Dynamic Price Band}, Year], PRE_AGG)
),
${ShelfChangesValueParam} = "Average $ Net Price",
ifelse(
${ShelfChangesParam} = 'Brands',
avgOver({net_price}, [{brand_name}, Year], PRE_AGG),
${ShelfChangesParam} = 'Retailers',
avgOver({net_price}, [{merchant_name}, Year], PRE_AGG),
avgOver({net_price}, [{Dynamic Price Band}, Year], PRE_AGG)
),
${ShelfChangesValueParam} = "Average $ Promotion",
ifelse(
${ShelfChangesParam} = 'Brands',
avgOver({shelf_price} - {net_price}, [{brand_name}, Year], PRE_AGG),
${ShelfChangesParam} = 'Retailers',
avgOver({shelf_price} - {net_price}, [{merchant_name}, Year], PRE_AGG),
avgOver({shelf_price} - {net_price}, [{Dynamic Price Band}, Year], PRE_AGG)
),
ifelse(
${ShelfChangesParam} = 'Brands',
avgOver(({shelf_price} - {net_price}) / {shelf_price}, [{brand_name}, Year], PRE_AGG) * 100,
${ShelfChangesParam} = 'Retailers',
avgOver(({shelf_price} - {net_price}) / {shelf_price}, [{merchant_name}, Year], PRE_AGG) * 100,
avgOver(({shelf_price} - {net_price}) / {shelf_price}, [{Dynamic Price Band}, Year], PRE_AGG) * 100
)
- Problem:
Everything works fine when using Brand or Retailer.
But when trying to use Dynamic Price Band inside distinctCountOver(), I get this error:
| Field | Value |
|---|---|
| region | us-east-1 |
| timestamp | 1745835730301 |
| requestId | 630a2ef0-a4b1-4702-8068-b721c3e45418 |
| sourceErrorCode | DISTINCTCOUNTOVER_INVALID_ARGUMENT |
| sourceType | SPICE |
The issue only happens at the placement calculation — when involving {Dynamic Price Band} inside distinctCountOver().
Everything is working fine except the line - distinctCountOver(concat({part_number},{merchant_name}), [{Dynamic Price Band}, Year], PRE_AGG)
- Additional Info:
{brand_name}and{merchant_name}are dataset fields.{Dynamic Price Band}is a calculated field inside the analysis (not a dataset field).- We cannot modify the underlying dataset to add any field.
- My Question:
- Is there a way to allow
distinctCountOverto work when grouping by a dynamic calculated field like{Dynamic Price Band}? - Or is there a recommended workaround to achieve this ?
We need to keep the calculations inside the Analysis layer only.
-
More detailed Context of Our goal is :- Urgent_Dynamic Percentage Calculation based on 2 columns - #6 by lary_andr
-
Formula of Dynamic Price Band -
ifelse(
${PriceBandSelection} = 'Ultra Low $10-$40',
ifelse(
{net_price} <= 9, '$9 & Below',
ifelse(
{net_price} <= 19, '$10 to $19',
ifelse(
{net_price} <= 29, '$20 to $29',
ifelse(
{net_price} <= 39, '$30 to $39',
'$40 & Above'
)
)
)
),
${PriceBandSelection} = 'Very Low $50-$200',
ifelse(
{net_price} <= 49, '$49 & Below',
ifelse(
{net_price} <= 99, '$50 to $99',
ifelse(
{net_price} <= 149, '$100 to $149',
ifelse(
{net_price} <= 199, '$150 to $199',
'$200 & Above'
)
)
)
),
${PriceBandSelection} = 'Low $100-$400',
ifelse(
{net_price} <= 99, '$99 & Below',
ifelse(
{net_price} <= 199, '$100 to $199',
ifelse(
{net_price} <= 299, '$200 to $299',
ifelse(
{net_price} <= 399, '$300 to $399',
'$400 & Above'
)
)
)
),
${PriceBandSelection} = 'Medium $200-$800',
ifelse(
{net_price} <= 199, '$199 & Below',
ifelse(
{net_price} <= 399, '$200 to $399',
ifelse(
{net_price} <= 599, '$400 to $599',
ifelse(
{net_price} <= 799, '$600 to $799',
'$800 & Above'
)
)
)
),
${PriceBandSelection} = 'High $300-$1200',
ifelse(
{net_price} <= 299, '$299 & Below',
ifelse(
{net_price} <= 599, '$300 to $599',
ifelse(
{net_price} <= 899, '$600 to $899',
ifelse(
{net_price} <= 1199, '$900 to $1199',
'$1200 & Above'
)
)
)
),
${PriceBandSelection} = 'Very High $500-$2000',
ifelse(
{net_price} <= 499, '$499 & Below',
ifelse(
{net_price} <= 999, '$500 to $999',
ifelse(
{net_price} <= 1499, '$1000 to $1499',
ifelse(
{net_price} <= 1999, '$1500 to $1999',
'$2000 & Above'
)
)
)
),
${PriceBandSelection} = 'Ultra High $1000-$4000',
ifelse(
{net_price} <= 999, '$999 & Below',
ifelse(
{net_price} <= 1999, '$1000 to $1999',
ifelse(
{net_price} <= 2999, '$2000 to $2999',
ifelse(
{net_price} <= 3999, '$3000 to $3999',
'$4000 & Above'
)
)
)
),
${PriceBandSelection} = 'Extremely High $1499-$6000',
ifelse(
{net_price} <= 1499, '$1499 & Below',
ifelse(
{net_price} <= 2999, '$1500 to $2999',
ifelse(
{net_price} <= 4499, '$3000 to $4499',
ifelse(
{net_price} <= 5999, '$4500 to $5999',
'$6000 & Above'
)
)
)
),
'No Price Band'
)
Regards,
Nikhil.
