SET ANALYSIS WITH DATES NOT WORKING

[#1]

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

[Sokkorn]

Hi Carel,

Try this

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

Make sure your [DATE] is number format.

Regards,

Sokkorn

[Gysbert_Wassenaar]

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

[tresesco]

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.

[#5]

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)

[#6]

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.

[#7]

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.

[Gysbert_Wassenaar]

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.

[#9]

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)

[jagannalla]

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