Built in functions within set analysis

[#1]

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

[swuehl]

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)

[#3]

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.

[sparur]

Hi

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.

[#5]

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

[sparur]

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.

[cesaraccardi]

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

[#8]

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

[sparur]

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.

[#10]

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