Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate the average / percentage of the last 30 days

Hi there, I've been looking through the forum and trying out some solutions, but so far not successful. That's why I'm posting my question anyway.

I'm analyzing email data and I have a deliverability measure:

num(
COUNT(Distinct IF([Result: Hard Bounce] = 0 AND [Result: Soft Bounce] = 0, [Result: Id]))
/
COUNT(Distinct [Result: Id])
,'#.##%')

Now I want a KPI chart to show this Deliverability Rate only of the last 30 days. I tried Below and Above examples from the forum and this IF statement:

IF 
( [Email: Date Sent] >= (Today()-30)

So far, I only get blank outputs. Please advise and/or provide me with syntax. Thanks!

Labels (4)
6 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Something like this:

num(
COUNT({$<[Result: Hard Bounce] = {0}, [Result: Soft Bounce] = {0}, [Email: Date Sent] ={">=$(=Today()-30)"} >} DISTINCT [Result: Id])
/
COUNT({$<[Email: Date Sent] ={">=$(=Today()-30)"} >} Distinct [Result: Id])
,'#.##%')

edit: corrected syntax for today() condition

Anonymous
Not applicable
Author

Thanks, but still doesn't work. My date field is a Date/Time format, could that be the issue?
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Shouldn't be an issue, as long as it's being interpreted as a timestamp rather than just text.

Does this work? = COUNT({$<[Email: Date Sent] ={">=$(=Today()-30)"} >} Distinct [Result: Id])

Does Max([Email: Date Sent]) work? - If not, that may be an indication that your timestamp isn't being loaded in as a timestamp

Anonymous
Not applicable
Author

You're right, I get invalid dimension errors if I try that. At the same time, it does auto recognize the field as a date and does all the calendar dimensions... What should I do?
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Note sure I understand what you're doing here - you should be entering these as expressions not dimensions.

 

Anonymous
Not applicable
Author

I am...

invalid dimension.PNG