Need to take the distinct count of product without using table calc

Question & Answer
1

Hi,

Can someone help me write the below calculation? (it has to be in week granularity)

if new_price < old_price then distinct_count(product)

I have tried writing this calc but quicksight is throwing an error,

distinctCountOver
(
ifelse({new_price<old_price}=1,distinct_count(product),NULL),
[truncDate(‘WK’,{date})],PRE_FILTER
)

Thanks

2

Are new_price and old price columns in your dataset without any calculation?

I have something that is working but it might not depending on your new / old price.

ifelse(avg({price})> lag(avg({price}),[truncDate(‘WK’,{event_timestamp}) DESC],1),distinct_count({product}),NULL)

This will check if the avg price over the week is greater than the previous weeks price avg. If it is then you distinct count the product.

Let me know if that helps.