topic Re: What is wrong with my equation? or set analysis? in QlikView

What is wrong with my equation? or set analysis?

Tue, 26 Aug 2014 22:59:52 GMT

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)

Re: What is wrong with my equation? or set analysis?

Anonymous — Tue, 26 Aug 2014 23:15:52 GMT

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"

Re: What is wrong with my equation? or set analysis?

Tue, 26 Aug 2014 23:46:12 GMT

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

Re: What is wrong with my equation? or set analysis?

Anonymous — Wed, 27 Aug 2014 01:20:09 GMT

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

Re: What is wrong with my equation? or set analysis?

jagan — Wed, 27 Aug 2014 01:35:10 GMT

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.

Re: What is wrong with my equation? or set analysis?

Wed, 27 Aug 2014 13:41:17 GMT

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

Re: What is wrong with my equation? or set analysis?

Anonymous — Wed, 27 Aug 2014 13:51:40 GMT

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.

Re: What is wrong with my equation? or set analysis?

Wed, 27 Aug 2014 19:37:16 GMT

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.

Re: What is wrong with my equation? or set analysis?

Wed, 27 Aug 2014 19:52:16 GMT

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.

Re: What is wrong with my equation? or set analysis?

jagan — Thu, 28 Aug 2014 02:00:25 GMT

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.

Re: What is wrong with my equation? or set analysis?

Thu, 18 Sep 2014 19:22:12 GMT

Hi, Jagan,

I would like to delete or close this thread as you requested. However, it comes out that I am not authorized to delete this post. How can I close it?

Re: What is wrong with my equation? or set analysis?

Anonymous — Fri, 19 Sep 2014 00:20:26 GMT

Mark the most useful answer as "Correct"

Re: What is wrong with my equation? or set analysis?

jagan — Fri, 19 Sep 2014 01:19:54 GMT

Hi,

AS msolomov suggested please give correct answer to the most useful post which helps you in getting the solution.

Regards,

Jagan.