I'm displaying a data value for "yesterday's" average transaction amount using the following expression:
if([TransactionDate]=today()-1,rangeavg(above(sum (if ([Product Name]='ProductX'
and [TransactionValue]>0, [TransactionValue]))/Sum (if([Product Name]='ProductX'
and [TransactionValue]>0, [TransactionCount])), 0, 7)))
Because this expression is displaying the average for yesterday, and we do business only Monday through Friday, the data value does not appear on the trend graph on Sundays or Mondays. I need to be able to display on both Sunday and Monday the average for the previous Friday. I'm attempting to do this using the "weekday" function but not having any success.
Thoughts as to how I can achieve this?
