topic Re: Changing a Variable. in QlikView

Changing a Variable.

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

So I am changing a the date on a variable I created in the Variable Overview.

However when I do this my formula stops working and doesn't give me what I want.

Variables are

vMonth = 01/12/2017

vPreviousMonth = 01/11/2017

and my formula to return the number of entries that are in 12/2017 is

=num(sum(if(month.1=vMonth,1,0)),'#,##0')

and for 11/2017:

=num(sum(if(month.1=vPreviousMonth,1,0)),'#,##0')

Any ideas as to why its not working would be greatly appreciated.

Thanks

Re: Changing a Variable.

zebhashmi — Thu, 01 Feb 2018 17:04:13 GMT

try that

=num(sum(if(month.1=$(vMonth),1,0)),'#,##0')

Re: Changing a Variable.

Anonymous — Mon, 12 Feb 2018 10:10:07 GMT

No that didn't work unfortunately.

I created them in the script now like this:

handlerinfoqvd:
LOAD Team, month,

key1

from $(QVDPath)handlerinfo.qvd (qvd);
Months:
load max(month) as vMonth,
(max(month)-1) as vPreviousMonth
from $(QVDPath)handlerinfo.qvd (qvd);

vMonth is working however vPreviousMonth isn't.

Re: Changing a Variable.

tresB — Mon, 12 Feb 2018 10:14:52 GMT

How do the values in your month field look like?

Re: Changing a Variable.

Anonymous — Mon, 12 Feb 2018 10:18:26 GMT

This is handlerinfo.qvd table

Re: Changing a Variable.

tresB — Mon, 12 Feb 2018 10:28:47 GMT

So your month field is actually having date values. Try like:

MonthStart(max(month) , -1) as vPreviousMonth

Re: Changing a Variable.

Anonymous — Mon, 12 Feb 2018 10:56:39 GMT

No that doesn't seem to work for me but thanks.

I also tried the below but no luck.

handlerinfoqvd:
LOAD Team, month,

key1

from $(QVDPath)handlerinfo.qvd (qvd);
Months:
load max(month) as vMonth,
if(right(max(month),2)='01',max(month)-89,max(month)-1) as vPreviousMonth
from $(QVDPath)handlerinfo.qvd (qvd);

Re: Changing a Variable.

tresB — Mon, 12 Feb 2018 11:03:42 GMT

What is the output you are getting using the statement I suggested? Could you share your qvd with sample data?

Re: Changing a Variable.

Anonymous — Mon, 12 Feb 2018 11:16:54 GMT

The monthstart worked.

Thank you very much.