topic Re: Re: Re: Built in functions within set analysis in QlikView

Built in functions within set analysis

Tue, 20 Aug 2013 08:28:32 GMT

Hi,

I need an expression which counts the EMP_ID when the end date is between today and seven days from today

I tried to accomplish thus using the expression

count({$<End={'>=$(=date(today(),'MM/DD/YYYY'))'}>
+
$<Planned_End={'<=$(=date(today(),'MM/DD/YYYY')+7)'>}>}EMP_Id)

But,its not giving desirable results.Can anyone help me with the same

Note:I need this to be done using set analysis and I do not want to make any changes in load scripting

Re: Built in functions within set analysis

swuehl — Tue, 20 Aug 2013 08:42:50 GMT

If there are two fields for your end dates, try:

=count({$<End={">=$(=date(today(),'MM/DD/YYYY'))"}, Planned_End={"<=$(=date(today()+7,'MM/DD/YYYY'))"} >} EMP_Id)

if there is in fact only one field (say End), try

=count({$<End={">=$(=date(today(),'MM/DD/YYYY'))<=$(=date(today()+7,'MM/DD/YYYY'))"} >} EMP_Id)

Re: Built in functions within set analysis

Tue, 20 Aug 2013 10:35:00 GMT

The expression suggested above is not giving the desirable count it gives the count as zero even though there are entries that satisfy the given condition.

Re: Built in functions within set analysis

sparur — Tue, 20 Aug 2013 10:51:05 GMT

I recommend to create a special variable vToday with definition: =date(today(1), ''MM/DD/YYYY') and vToday_7: date(today(1)-7, 'MM/DD/YYYY')

use these variables in the set analysis:

count({<End = {"<=$(vToday)>=$(vToday_7)"}>} EMP_Id). But you should be sure that values of End field have the same date format ('MM/DD/YYYY'). That's why I usually reccomend to use numeric for dates. so you shouldn't to think about correct format and alayse use numeric.

Re: Built in functions within set analysis

Tue, 20 Aug 2013 11:05:13 GMT

Its not working.I am unable to find the option to attach the application here,through which it would have been much easier for me to showcase it

Re: Built in functions within set analysis

sparur — Tue, 20 Aug 2013 11:09:15 GMT

when you reply here, in the top of the right corner you will see oprion "Use advanced editor". where you can attach a file. try it.

Re: Built in functions within set analysis

cesaraccardi — Tue, 20 Aug 2013 11:18:26 GMT

Hi Anushree,

It's very likely to be a formatting issue. I am assuming the DateFormat variable (at the top of your loading script) is probably defined as DD/MM/YYYY and the field End has the following format MM/DD/YYYY. If so, I would suggest you to do one of the following:

1. If all of your Date fields are formatted as MM/DD/YYYY, you could change the DateFormat variable and reload the application. By doing this you won't need to worry about the format in the set modifier anymore.

2. If it's only this or a few particular fields that are formatted as MM/DD/YYYY, you could change the format during the load:

Load
(...),
Date(Date#(End,'MM/DD/YYYY')) as End
FROM
TABLE;

And your expression would be:

A. If you want to select all where End greater or equal than Today AND Planned_End lesser or equal than Today + 7 days:

count({$<End={'>=$(=date(today()))'},Planned_End={'<=$(=date(today()+7))'>}>} EMP_Id)

B. If you want to select all where End greater or equal than Today OR Planned_End lesser or equal than Today + 7 days:

count({$<End={'>=$(=date(today()))'}>+$<Planned_End={'<=$(=date(today()+7))'>}>} EMP_Id)

Regards,

Cesar

Re: Re: Built in functions within set analysis

Wed, 21 Aug 2013 03:41:38 GMT

Hi,

Anushree here i was not able to find the advance editor option in my account thus using my friends account to post the application ,as said here i have trying to count the number of employee Ids whose end date is greater than equal to todays and lesser than equal to today+7 days (meaning end date is between today and today+7 days).Please have a look and let me know why the application is not showing the count as 1 even when the planned date is 25-08-2013

Re: Re: Re: Built in functions within set analysis

sparur — Wed, 21 Aug 2013 06:40:07 GMT

Hi.

I see 2 issues with your data:

- End field have text format. so you should convert it to data format in the script.

- in Count() function you use incorrect fieldname EMP_Id. QlikView is case sensative tool. You should use Emp_Id field.

Please see my attached example.

Re: Re: Re: Built in functions within set analysis

Wed, 21 Aug 2013 07:00:47 GMT

I am unable to find the attachment,it could be because the advance editor option is not available in my profile.Can you please help me get it

Re: Re: Re: Re: Built in functions within set analysis

sparur — Wed, 21 Aug 2013 07:07:32 GMT

what is your email? I will send my example