Hi ,
I’m building a parameter-driven line chart in Quick Sight titled “Average {Parameter} by Brand Trend”, where users can toggle between the following metrics using a parameter $PromoTrendValueParam4 :
- Average $ Shelf Price
- Average $ Net Price
- Average $ Promotion
- Average % Discount
Below is the current setup we have :
Where :
DynamicPromoTrendValue4 :
ifelse(${PromoTrendValueParam4}="Average $ Shelf Price",avg({shelf_price}),${PromoTrendValueParam4}="Average $ Net Price",avg({net_price}),${PromoTrendValueParam4}="Average $ Promotion",avg({Promotion Value}),(avg(Discount)*100))
DynamicPromoTrend :
ifelse(${PromoTrendParam}="Brand",{brand_name}, {merchant_name})
My goal:
In my line chart, I want to exclude months where Promotion or Discount values are 0 so that those data points are not shown on the chart. However, if Net Price or Shelf Price exists for that same month, I still want those values to be visible. I do not want to use visual filters to achieve this, because applying a filter like “Discount greater than 0” removes the entire month from the chart — including valid Net or Shelf Price data, which I need to keep. My goal is to handle this logic entirely within the calculated field, so that only Promotion and Discount lines skip months with 0 values, while Net and Shelf Price lines remain continuous.
So we want to achieve this by doing changes in the below calculated field :
DynamicPromoTrendValue4 :
ifelse(${PromoTrendValueParam4}="Average $ Shelf Price",avg({shelf_price}),${PromoTrendValueParam4}="Average $ Net Price",avg({net_price}),${PromoTrendValueParam4}="Average $ Promotion",avg({Promotion Value}),(avg(Discount)*100))
Where
Promotion value : {shelf_price}-{net_price} and
Discount : {Promotion Value}/{shelf_price}
I have Tried below calculated field but we are not getting correct values for avg promotions and avg discount :
avg( ifelse( ${PromoTrendValueParam4} = "Average $ Shelf Price", {shelf_price}, ${PromoTrendValueParam4} = "Average $ Net Price", {net_price}, ${PromoTrendValueParam4} = "Average $ Promotion" AND {Promotion Value} = 0, NULL, ${PromoTrendValueParam4} = "Average $ Promotion", {Promotion Value}, ${PromoTrendValueParam4} = "Average % Discount" AND {Discount} = 0, NULL, ${PromoTrendValueParam4} = "Average % Discount", {Discount} * 100, NULL ) )
Where
Promotion value : {shelf_price}-{net_price} and
Discount : {Promotion Value}/{shelf_price}
For E.g. For July 2024 Promotion value should be 1 as it is shelf price - net price (156-155) but it is coming as 42 by above formula same is happening with discounts (for net price and shelf price it is working fine)
Regards,
Nikhil.
