Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What is wrong with my equation? or set analysis?

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)

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

12 Replies
Anonymous
Not applicable
Author

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"

Not applicable
Author

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
Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.