topic Re: Set Analysis Greater Than Date in QlikView

Set Analysis Greater Than Date

Tue, 23 Jun 2015 21:13:33 GMT

Hi,

I am trying to compare the dates and get a sum like

Sum my field where Activity_Date is greater than Assignment_Start_Date and it is giving me the same value as Sum(FieldName).

Please help with the syntax as I am missing something...

=Count({<Activity_Date={">=$(Assignment_Start_Date)"}>}FieldName)

Re: Set Analysis Greater Than Date

sunny_talwar — Tue, 23 Jun 2015 21:25:13 GMT

Try this:

=Count({<Activity_Date = {"=Activity_Date >= Assignment_Start_Date"}>} FieldName)

Re: Set Analysis Greater Than Date

Anonymous — Tue, 23 Jun 2015 21:26:58 GMT

=Count({<Activity_Date={">=$(=max(Assignment_Start_Date))"}>}FieldName)

Re: Set Analysis Greater Than Date

Tue, 23 Jun 2015 21:37:18 GMT

Thanks Michael

it is almost working but it is doing Activity_Date>Assignment_Start_Date and I need Activity_Date>=Assignment_Start_Date

Re: Set Analysis Greater Than Date

sunny_talwar — Tue, 23 Jun 2015 21:40:17 GMT

Did you try this?

=Count({<Activity_Date = {"=Activity_Date >= Assignment_Start_Date"}>} FieldName)

Re: Set Analysis Greater Than Date

Tue, 23 Jun 2015 21:42:54 GMT

Yes SunIndia and it gives 0

Re: Set Analysis Greater Than Date

sunny_talwar — Tue, 23 Jun 2015 21:43:56 GMT

Assignment_Start_Date is a field from the script, right?

Would you be able to share a sample?

Re: Set Analysis Greater Than Date

Tue, 23 Jun 2015 21:48:24 GMT

Yes both are date fields from the script.... it is a simple comparison and then doing a sum...

Re: Set Analysis Greater Than Date

sunny_talwar — Tue, 23 Jun 2015 21:51:58 GMT

Try this:

=Count({<Activity_Date = {"=Num(Activity_Date) >= Num(Assignment_Start_Date)"}>} FieldName)

and before you try the above expression, just make sure you get numbers in a text box when you do Num(Activity_Date) and Num(Assignment_Start_Date)

Best,

Sunny

Re: Set Analysis Greater Than Date

Josh_Good — Tue, 23 Jun 2015 22:03:21 GMT

Hi FAIQ,

The most likely reason you are having trouble here is that the data in your set analysis expression are in a different format, possibly due to the dollar sign expansion. Try forcing the Assignment_Start_Date value to be in the same date format as the Activity_Date value.

So if the Activity_Date in this format: YYYY/MM/YY then try this expression

=Count({<Activity_Date={">=$(Date(Assignment_Start_Date,'YYYY/MM/YY'))"}>}FieldName)

-Josh

Qlik

Re: Set Analysis Greater Than Date

Wed, 24 Jun 2015 14:34:55 GMT

=Count({<Activity_Date={">=$(=max(Assignment_Start_Date))"}>}FieldName)

is working fine now. There was a difference in my date formats so now they are the same and is working like a charm...

Re: Set Analysis Greater Than Date

Wed, 24 Jun 2015 16:38:44 GMT

Have a twist to this .. The below code works if one employee is selected and gives zero when grid is allowed to display all employees.

What is missing now?

=Count({<Activity_Date={">=$(=max(Assignment_Start_Date))"}>}FieldName)

Re: Set Analysis Greater Than Date

Josh_Good — Wed, 24 Jun 2015 18:11:01 GMT

Faiq,

I believe this is because the set analysis is being applied to the entire data set when you actually need it to apply for each employee in the grid. To do this you will need use the AGGR function. Below I've guest at the correct expression. If what I posted doesn't work please post a sample QVW so we can work it out for you.

=sum(Aggr(Count({<Activity_Date={">=$(=max(Assignment_Start_Date))"}>}FieldName),Employee))

-Josh

Qlik

Re: Set Analysis Greater Than Date

Wed, 24 Jun 2015 18:48:48 GMT

Thanks for looking into Josh. I tried a simple If statement and is working now. Will let you know if more errors appear in testing

=Count(if(Activity_Date>=Assignment_Start_Date, Activity_GUID))