topic Re: Re: SET ANALYSIS WITH DATES NOT WORKING in QlikView

SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 10:10:41 GMT

CAN SOMEONE PLEASE HELP ME. I AM NOW AT A POINT WHERE I WANT TO BREAK MY COMPUTER LOL.

WHY DOESNT THIS WANT TO WORK CORRECTLY:

sum( {$<DATE = {'>=$(=weekstart(max(DATE)))<=$(=max(DATE))'}>} timeworked )

IT STILL PULLS ALL THE DATA AND NOT ONLY THIS WEEKS DATA...

i EVEN TRIED ADDING THE NUM() FUNCTION, DOENST WORK EITHER

Re: SET ANALYSIS WITH DATES NOT WORKING

Sokkorn — Wed, 18 Sep 2013 10:17:08 GMT

Hi Carel,

Try this

=Sum({<DATE = {">=$(=Num(WeekStart(Max(Date))))<=$(=Date(Max(Date)))"} >} timeworked)

Make sure your [DATE] is number format.

Regards,

Sokkorn

Re: SET ANALYSIS WITH DATES NOT WORKING

Gysbert_Wassenaar — Wed, 18 Sep 2013 10:17:58 GMT

Your caps lock key seems to be broken already. Please refrain from posting until you bought a new keyboard.

Re: SET ANALYSIS WITH DATES NOT WORKING

tresB — Wed, 18 Sep 2013 10:18:02 GMT

Try this:

sum( {$<DATE = {">=$(=weekstart(max(DATE)))<=$(=max(DATE))"}>} timeworked )

If doesn't work, check if the DATE field is really in date format or string. Best would be, if you can share a sample app.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 10:18:03 GMT

Try the following:

sum( {$<DATE = {">=$(vWeekStart)<=$(vWeekEnd)"}>} timeworked )

And create a variable that computes the start and end of the week.

If you don't like creating variables try the following syntax:

sum( {$<DATE = {"$(>weekstart(max(date))<$(=max(date)"} timeworked)

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 10:21:42 GMT

HI,

Thanks for helping but still not working.

=Sum({<DATE = {'>=$(=Num(WeekStart(Max(DATE))))<=$(=Date(Max(DATE)))'} >} timeworked)

I basically want to only show all transactions in the last week, and use the DATE field to do it.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 10:27:56 GMT

Ha ha Wassenaar.

DATE field is loaded in script with this formuala:

Date(FLOOR([worklog_created]),'DD/MM/YYYY') AS DATE

DATE as integer is showing example 41,535, so I assume it is definitely not a text field.

Re: SET ANALYSIS WITH DATES NOT WORKING

Gysbert_Wassenaar — Wed, 18 Sep 2013 10:35:21 GMT

Try formatting the dates: sum( {$<DATE = {'>=$(=date(weekstart(max(DATE))))<=$(=date(max(DATE)))'}>} timeworked ). Perhaps you also need to specify the date format: ...date(max(DATE),'DD/MM/YYYY')...etc

If that doesn't help please post a sample document that demonstrates the problem.

In general it's a good idea to load dates as numbers only without any date format and do the formatting only in the objects on the sheets. That makes it easier to work with dates in set analysis expressions.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 10:47:41 GMT

Hi,

In Script, calculate the numeric value equivalent to the date field,

floor(num(Date([worklog_created],'DD/MM/YYYY'))) AS DATE_NUM

and write the expression as below in set,

Sum({<DATE_NUM = {'>=$(=floor(Num(WeekStart(Max(Date))))) <=$(=floor(num(Date(Max(Date)))))'} >} timeworked)

Re: Re: SET ANALYSIS WITH DATES NOT WORKING

jagannalla — Wed, 18 Sep 2013 10:54:29 GMT

Hi,

Why don't you try first with hard code values i.e. static values inside set analysis.

If it works perfectly then check the formula first in text object. If you are able to see values then integrate in expression.

Please share app with sample real data if you are not able to do.

Thanks,

Jagan

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:05:47 GMT

I have realised that the issue is the MAX function. It gives an error, any ideas why?

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:10:02 GMT

Carel,

please check in your expression editor if DATE is highlighted red or blue. If it's blue QV is not recognizing DATE as a field name but as a function.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:14:07 GMT

How you are saying there is a problem with max function.

can you explain briefly and if u can able to attach sample file will be useful to find the issue.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:23:56 GMT

Hi,

I attached the model,

Thanks

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:39:32 GMT

try the following expression,

=sum({<DATE={">=$(=date(weekstart(max(DATE)),'YYYY/MM/DD')) <=$(=date(max(DATE),'YYYY/MM/DD'))"}>}timeworked)

there is no data for 30th sep 2013.

select the date 18th sep, or select aug month or select any month where u have the data.

Re: Re: SET ANALYSIS WITH DATES NOT WORKING

Gysbert_Wassenaar — Wed, 18 Sep 2013 12:41:05 GMT

To begin with the max Date is 30/09/2013 and there's no data for that date. If you select for example 18/09/2013 there is data. But you need to specify the date format YYYY/MM/DD. See attached qvw.

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:41:34 GMT

Hey Carel,

the following formula works in your app.

=sum({<date={'>$(=(floor(num(max(DATE))))-7)'}>}timeworked)

Re: SET ANALYSIS WITH DATES NOT WORKING

Wed, 18 Sep 2013 12:55:00 GMT

Thanks everyone, sorted