Calculate the average / percentage of the last 30 days

[Anonymous]

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!

[marcus_malinow]

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]

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

[marcus_malinow]

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]

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]

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

[Anonymous]

I am...