Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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!

6 Replies
Highlighted
Specialist III
Specialist III

Re: Calculate the average / percentage of the last 30 days

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

Highlighted
Contributor II
Contributor II

Re: Calculate the average / percentage of the last 30 days

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

Re: Calculate the average / percentage of the last 30 days

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

Highlighted
Contributor II
Contributor II

Re: Calculate the average / percentage of the last 30 days

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?
Highlighted
Specialist III
Specialist III

Re: Calculate the average / percentage of the last 30 days

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

 

Highlighted
Contributor II
Contributor II

Re: Calculate the average / percentage of the last 30 days

I am...

invalid dimension.PNG