topic Re: Create expression in Script rather than Front End in QlikView

Create expression in Script rather than Front End

Thu, 14 Nov 2013 14:39:30 GMT

Hi guys,

This should be pretty straight forward, but it is not working as expected for me.

I have 5 expressions as follows

sum(C1_K)/sum({<C_Date={'<=$(=today()-1)'}>}Value)

sum(C21_K)/sum({<C_Date={'<=$(=today()-21)'}>}Value) and so on

The expressions cause my front end to slow down when this chart is displayed.

I would like to move this to the script to simp-lify the calculation.

I tried if(C_Date = today()-1, 1,0) as C1_K_Flag

When I put this back in my expression as sum(C1_K)/sum(C1_K_Flag * Value), it doesn't work and comes up with a blank chart.

Can someone please tell me what I am doing wrong?

Thanks

Re: Create expression in Script rather than Front End

Clever_Anjos — Thu, 14 Nov 2013 15:59:36 GMT

Instead of moving all calcutations to script, I could create all variables into your script with this code

for i = 1 to 31
  Let varname='vC'&i&'_K';
  Let varvalue=date(Today() - i);
  Let $(varname) = '$(varvalue)';
next

And then use

Sum(C1_K)/sum({<C_Date={'<=$(vC1_K)'}>}Value)

Re: Create expression in Script rather than Front End

swuehl — Thu, 14 Nov 2013 16:14:02 GMT

Your script flag is checking for C_Date equal today()-1 while your set modifer is using a date range (<=today()-1).

Could this explain something? If you don't have data for C_Date equal today()-1, all flags are zero and your expression won't return anything (div by zero).

Re: Create expression in Script rather than Front End

Fri, 15 Nov 2013 08:54:29 GMT

Thanks for all the responses. Swuehl, thanks for spotting my obvious mistake. I wasn't using the right expression in my load script because I was ignoring the <= sign.

Everything works fine now.

Many thanks