8 Replies Latest reply: Sep 9, 2016 8:24 AM by Simon Hynd

# Set Analysis - Greater than Date Expression

I'm trying to create an expression to give me the sum of HrsWorked when the TransDate is greater than 01/01/2014 but I'm struggling sorry.

I've figured out how to get the TransDate equal to 01/01/2014

Sum({\$<TransDate={"01/01/2014"}>}Hrsworked)

but struggling to get the greater than operator to work .

• ###### Re: Set Analysis - Greater than Date Expression

Sorry guys,

I've managed to get this working now with this:

Sum({<TransDate={'>=01/01/2014'}>}Hrsworked)

but want to add another condition to it based on the day of the week being a certain day.

I'm using a day of the week field '=weekday([TransDate.autoCalendar.Date])' as a filter which works correctly but I want to add this as another condition.

How would I do the above to show data for dates after 01/01/2014 and only day of the week = 'Monday'?

• ###### Re: Set Analysis - Greater than Date Expression

You can add a modifier on week field like

Sum({<TransDate={'>=01/01/2014'}, [week field] = {'Monday'}>}Hrsworked)

Maybe you need to use 'Mo' instead of Monday, whatever is the value in your field.

• ###### Re: Set Analysis - Greater than Date Expression

Can't get it working sorry. I don;t have a field called 'week field' is that why it's not working?

If so how do I create one?

Sorry for the dumb questions, i'm still trying to find my way around qlik sense.

• ###### Re: Set Analysis - Greater than Date Expression

I think it would be better if you can create a field in the script using WeekDay function:

WeekDat(Date) as WeekDay

and then may be like this:

Sum({<TransDate={'>=01/01/2014'}, WeekDay = {'Mon'}>}Hrsworked)

To check if you need to put Mon or Mo or Monday, check your environmental variable

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='\$#,##0.00;(\$#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

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

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

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

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

• ###### Re: Set Analysis - Greater than Date Expression

Thanks very much for your help Sunny, I really appreciate it.

• ###### Re: Set Analysis - Greater than Date Expression

Looks like you are using an autogenerated calendar.

Open the data editor and edit the auto-generated section to add the weekday field (if it's not already there):

Now you can use this field in your set analysis:

Sum({<TransDate={'>=01/01/2014'}, [TransDate.autoCalendar.WeekDay] = {'Mo'}>}Hrsworked)

Again, check which value for Monday you need to use.

Alternatively, you can create a master calendar like shown here

The Master Calendar

Then add all attribute fields like Weekday etc. using

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/date-time-functions.h…

Or just add (few) calendar attribute fields directly to your data table:

Customer,

TransactionDate,

WeekDay(TransactionDate) as TransactionWeekDay,

Month( TransactionDate) as TransactionMonth,

Year( TransactionDate) as TransactionYear,

...

But also have a look at:

Get the Dates Right

Why don’t my dates work?

Dates in Set Analysis

Hope this helps,

Stefan

• ###### Re: Set Analysis - Greater than Date Expression

Fantastic, thanks very much for your help, greatly appreciated!!

• ###### Re: Set Analysis - Greater than Date Expression

Maybe like

Sum({\$<TransDate={">01/01/2014"}>} Hrsworked)

Dates in Set Analysis