topic Re: Average in Script when in QlikView

Average in Script when

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the below expression but want to add this into the script. How would one write this? I also would like to remove zero values from the average.

Chart Properties Expression below:

Dimensions: Program

Avg({<ENROLLMENT_STATUS = {'Closed'},REASON ={'a','b'}>}DAYS)

Re: Average in Script when

Clever_Anjos — Tue, 14 Jun 2016 19:13:21 GMT

Do you need a level of aggregation?

Re: Average in Script when

sunny_talwar — Tue, 14 Jun 2016 19:15:49 GMT

Put another way, where did you use this in the front end? A chart or text box object? If it was in chart, what were the dimensions?

Re: Average in Script when

swuehl — Tue, 14 Jun 2016 19:27:26 GMT

Guessing from the sparse information provided, something like

LOAD

AVG(DAYS) AS AverageDays

RESIDENT YourTableWithAllFields

WHERE DAYS <>0 AND ENROLLMENT_STATUS = 'Closed' AND MATCH(REASON, 'a','b');

Re: Average in Script when

Anonymous — Tue, 14 Jun 2016 19:29:06 GMT

I see. I have updated my question. This was in the Chart Properties and my dimesion field is "Program". I would like the avg to calcuate based off my selections from the list box, but need to only avg when. Enrollment status = 'Closed', Reasons = 'a','b' and Days <> 0

Re: Average in Script when

sunny_talwar — Tue, 14 Jun 2016 19:30:32 GMT

May be like this:

LOAD Program,

Avg(DAYS) as AverageDays

RESIDENT YourTableWithAllFields

WHERE DAYS <>0 AND ENROLLMENT_STATUS = 'Closed' AND MATCH(REASON, 'a','b')

Group By Program;

UPDATE: Used Stefan's code and added the Program as the dimension

Re: Average in Script when

Clever_Anjos — Tue, 14 Jun 2016 19:35:57 GMT

Why don´t just this into your graph?

Avg({<ENROLLMENT_STATUS = {'Closed'},REASON ={'a','b'},DAYS=[">0"}>}DAYS)

Re: Average in Script when

Anonymous — Wed, 15 Jun 2016 12:29:25 GMT

I could and it's nice to now know the syntax. Are there advantages and disadvantages in doing this?

Re: Average in Script when

Anonymous — Wed, 15 Jun 2016 16:42:36 GMT

Thanks, I'm almost there. I have in my where clause the string don't. I believe the apostrophe is throwing an error. Is there a way for QV to except the apostrophe. I tried to use Double quotes, but that's not working!

Re: Average in Script when

swuehl — Wed, 15 Jun 2016 16:44:47 GMT

Try to use two single quotes within the string literal (and enclose with single quotes around).

Re: Average in Script when

sunny_talwar — Wed, 15 Jun 2016 16:46:14 GMT

Check here:

Escape sequences