What is wrong with my equation? or set analysis?

[#1]

I need to get a nuber of days between two dates and compare it if it greater than 0 and less or equal to 7.

So, I have come up with the equation below, but it said "error in set modifier expression."

what is wrong with that?

Count({<Num(SERVICE_ACT_TERM_DATE-SERVICE_DATE_START)={">0<=7"}>}PERSONID)

[Anonymous]

You cannot use an expression like this in set analysis.  It must be a field.  If you want to use set analysis, you need to create this difference as a separate field in the script:
num(SERVICE_ACT_TERM_DATE - SERVICE_DATE_START)as NewField
So you can try this:
Count({<NewField={">=<=7"}>} PERSONID)

Or maybe just don't use set analysis but an expression with "if"

[#3]

Thank you, Michael,

The "Service_ACT_TERM_Date" and "Service_DATE_START" are coming from sql server. Therefore, I cannot change any of it including the creation of new field. Or can I?

Can I just type in below in Script?

num(SERVICE_ACT_TERM_DATE - SERVICE_DATE_START)as NewField

[Anonymous]

Well, it is typical to get the data from a database, but it doesn't mean that you're restricted.  Use preceding load, like this:

Data:

LOAD

     Field1,

     Field2,

     <any valid QlikView function> as [Another Field],

     ...

;

SQL SELECT

     Field1,
     Field2,

     ...

FROM <your database table>;

Regards,

Michael

[jagan · Accepted solution]

Hi,

It is easier in front end if you deal this in backend script, because everywhere you don't need to do the same calculation.  Also the performance of the app is also good if calculations are done in script.

Data:

LOAD

    *,

     If(Num(SERVICE_ACT_TERM_DATE - SERVICE_DATE_START) > 0 AND

     Num(SERVICE_ACT_TERM_DATE - SERVICE_DATE_START) <= 7, 1, 0) AS Flag

;

SQL SELECT

    *

FROM <your database table>;

Now in front end you can just use below expression

Count({<Flag={1}>}PERSONID)

Hope this helps you.

Regards,

Jagan.

[#6]

I added and  tried

Data:

LOAD

     SERVICE_ACT_TERM_DATE,

     SERVICE_DATE_START,

     <SERVICE_ACT_TERM_DATE-SERVICE_DATE_START> as newfield

;

SQL SELECT

     SERVICE_ACT_TERM_DATE,

     SERVICE_DATE_START,

-------------------------------------------------------------------------------------------------------------------- and

Data:

LOAD

     SERVICE_ACT_TERM_DATE,

     SERVICE_DATE_START,

     num(SERVICE_ACT_TERM_DATE-SERVICE_DATE_START) as newfield

;

SQL SELECT

     SERVICE_ACT_TERM_DATE,

     SERVICE_DATE_START,

FROM data base,

But none of them are working

[Anonymous]

I'd use:

SERVICE_ACT_TERM_DATE - SERVICE_DATE_START as newfield

What is the format of the fields?  Maybe they are not real date/time fields and need some formatting?

When you make it to work, I recommend to use "Flag" approach per Jagan Mohan - it makes expression simpler.

[#8]

To all,

No one got the right answer. And, yes, I can use the expression like that.

Only things that I needed were if stament with num. It worked without having the complecated way as below.

count(if(num(SERVICE_ACT_TERM_DATE-SERVICE_DATE_START) <=7,PERSONID))

Thank you for replies.

[#9]

Sorry, I didn't mean you guys were wrong.

I meant it was not a 'simpler way,' which is easy for new users, like me, to understand.

I learned something from you guys. And I really appreciate that.

Thank you again.

[jagan]

Hi,

count(if(num(SERVICE_ACT_TERM_DATE-SERVICE_DATE_START) <=7,PERSONID))

The above expression works when there are both fields in the chart, what if the fields are not there in the chart?  The one we proposed is the best and efficient approach calculating in front end is not a good approach.   I think there is some issue with date formats that is why you are not get this done in script.  Try to avoid calculations in front end so that in future you won't get performance issues.

Please close this thread so that it would be helpful for those who are looking for similar solution.

Regards,

Jagan.