Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Champion III
Champion III

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"

Highlighted
Not applicable

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

Highlighted
Champion III
Champion III

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Not applicable

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

Highlighted
Champion III
Champion III

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.

Highlighted
Not applicable

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.

Highlighted
Not applicable

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.

Highlighted
MVP & Luminary
MVP & Luminary

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.