Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
paulien1989
New 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
marcus_malinow
Valued Contributor 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

paulien1989
New 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?
marcus_malinow
Valued Contributor 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

paulien1989
New 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?
marcus_malinow
Valued Contributor 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.

 

paulien1989
New Contributor II

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

I am...

invalid dimension.PNG