Date Set Analysis (again...)

[mikecrengland]

Morning -

We are evaluating whether or not to move from View to Sense and I've run into a problem that's driving me crazy. I have the following expression that works fine in View:

Sum({<WeekStartMon = {"$(=Date(WeekStart(Max(FullDate))))"},Planner *= >}EmptyMiles)

When I build a strait table in Sense, no luck. If I remove the date part of the set analysis, it works. What the heck have I done wrong?

Thanks,

mike

[sunny_talwar]

What is the format of WeekStartMon field? Is Date() function giving you the same format? If not, then may be you need to fix this in the environmental variable within the script or you can give the format directly within the date function of your set analysis

[mikecrengland]

Hi Sunny -

Both are in the format 'YYYY-MM-DD'. I checked 

=Date(WeekStart(Max(FullDate)))

using a text object and the result was 2018-10-28, which at first glance was correct. Now I realize that our WeekStart is a Monday and 2018-10-28 is a Sunday. Geez.

I fixed that in View with

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

but it didn't work... Do you know how to change the weekstart day?

[jobsonkjoseph · Accepted solution]

Hi Michael,

In Sense the default format is;

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Also try changing this to 7 or 1 and check;

SET FirstWeekDay=6;

[mikecrengland]

That worked!  SET FirstWeekDay=7; for Monday start..

Thanks!