Hi @Nikhilburhade,
Did you try already to replace the individual aggregation functions in DynamicPromoTrendValue4 with an ifelse check on 0?
Having something like this in mind: Replacing e.g. avg({shelf_price}) with ifelse(avg({shelf_price})=0,NULL,avg({shelf_price})) and proceeding the same way for the other 3 calculations.
The overall calculated field for DynamicPromoTrendValue4 would then look as follows:
ifelse(
${PromoTrendValueParam4}="Average $ Shelf Price",ifelse(avg({shelf_price})=0,NULL,avg({shelf_price})),
${PromoTrendValueParam4}="Average $ Net Price",ifelse(avg({net_price})=0,NULL,avg({net_price})),
${PromoTrendValueParam4}="Average $ Promotion",ifelse(avg({Promotion Value})=0,NULL,avg({Promotion Value})),
ifelse((avg(Discount)*100)=0,NULL,(avg(Discount)*100)))
In case this does not work in your case, would you mind building an example using an anonymized/mocked sample dataset in the Quick Sight Arena and share it here with the Community so that it’s easier to explore and share back a solution that will work for your specific case. Thanks!
Did this answer your question? If so, please help the community out by marking this answer as “Solution”!