topic Re: Expression inside a set expression in QlikView

Expression inside a set expression

Mon, 29 Sep 2014 11:41:01 GMT

I have a set expression which returns the number of employees fulfilling the below criteria

=Count (Distinct {$<Rundate={$(vRundate)}, Maxdate={'<$(vMaxdate)'} >} EmpID)

where vRundate is 201401 and vMaxdate is 201408

Now my query is that i want to put this in a chart where my dimension will be Month and vRundate and vMaxdate will change based on Month.

Right now its a variable and can store only one value at a time but since my chart will have Month as dimension and it will have values from Jan-Aug so my variable value should change for every month as highlighted in the table below.

Month	vRundate	vMaxdate
Jan	201401	201402
Feb	201402	201403
Mar	201403	201404
Apr	201404	201405
May	201405	201406
Jun	201406	201407
Jul	201407	201408

How can i do this ? Can we write expression inside a set expression and pass values of Rundate and Maxdate like Rundate= Max(Month)+1 or simething like this ?

Thanks a lot in advance,

Sumit

Re: Expression inside a set expression

anbu1984 — Mon, 29 Sep 2014 11:45:45 GMT

Yes you can write an expression inside set like below

=Count (Distinct {$<Rundate={$(= Max(Month)+1)}, Maxdate={'<$(vMaxdate)'} >} EmpID)

Re: Expression inside a set expression

Mon, 29 Sep 2014 12:02:07 GMT

Doesn't work. It doesn't take Max as a function

Re: Re: Expression inside a set expression

anbu1984 — Mon, 29 Sep 2014 12:49:30 GMT

Missed bracket in the expression

=Count (Distinct {$<Rundate={$(= Max(Month)+1))}, Maxdate={'<$(vMaxdate)'} >} EmpID)

Re: Expression inside a set expression

Mon, 29 Sep 2014 14:23:41 GMT

Bracket was already there. However giving extra bracket would give an error.

Re: Expression inside a set expression

lironbaram — Mon, 29 Sep 2014 14:36:54 GMT

in this case you can't use set analysis

as set analysis is like making a selection in the model , it will return the same results in all month

you can try something like assuming you have a date field

Count (if(Rundate=num(year(monthstart(Date))&num(month(monthstart(Date)))) and

Maxdate <num(year(monthstart(Date))&num(month(monthstart(Date)))+1), EmpID)

Re: Expression inside a set expression

Mon, 29 Sep 2014 15:06:41 GMT

You need to specify your variable names with generalised month variable without using the '=' sign in the expression for the variablr i.e. vRundate and vMaxdate.

Hope this helps

Re: Expression inside a set expression

anbu1984 — Mon, 29 Sep 2014 17:09:41 GMT

You are right. Can you post sample qvw.

Re: Expression inside a set expression

Mon, 29 Sep 2014 17:15:50 GMT

Was that reply for me?

Re: Expression inside a set expression

anbu1984 — Mon, 29 Sep 2014 17:17:59 GMT

No Gaurav

Re: Expression inside a set expression

Mon, 29 Sep 2014 17:20:44 GMT

Ok!

Re: Expression inside a set expression

Tue, 30 Sep 2014 07:08:09 GMT

Hi Gauarv,

I don't understand whatever you are saying. Could you please explain with an example.

Thanks a lot,

Sumit

Re: Expression inside a set expression

sasikanth — Tue, 30 Sep 2014 07:22:41 GMT

If you have two date fileds

then you can write the expression like

count({<rundate ={'$(=max(Left(date(rundate,'YYYYMMDD'),6))+1)'} ..... EMP_ID)

Re: Expression inside a set expression

Tue, 30 Sep 2014 09:56:46 GMT

What i'm trying to say is while defining your variables i.e. vRundate and vMaxdate, do not write a specific value like you have given which is 201401 and 201408. Instead give an expression which will take automatically the selected month or months into consideration and while doing it do not precede your variable expression with an '=' sign.