Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get the highest sales for a given day in a week by campaign.
Ex: Monday: $10,000, Tuesday $15,000, Wednesday $7000 etc.. For each campaign that I have i want to pull Tuesday's sales because that is the highest sale for that week.
Right now I am able to get the highest sales for the last day of the week by using the max date function.
if(sum({$<[GA Campaign]=,EmailCampaignYorN={'Email'}>}[Unique Opens]) / sum({$<[GA Campaign]=,EmailCampaignYorN={'Email'}>}Deliveries)>0,
SUM({<EmailCampaignYorN={'Email'},Date={'$(=max(Date))'}>}[Transaction Revenue]),NULL())
I am not sure how to approach this. I also attached a picture.
Also, you can ignore the if function this is just to get rid of some nulls.
May be create Week field from Date field like
Week(DateField) as Week
and, use expression as
if(sum({$<[GA Campaign]=,EmailCampaignYorN={'Email'}>}[Unique Opens]) / sum({$<[GA Campaign]=,EmailCampaignYorN={'Email'}>}Deliveries)>0,
SUM({<EmailCampaignYorN={'Email'},Date={'$(=max(Week))'}>}[Transaction Revenue]),NULL())