Set Analysis - Greater than Date Expression

[#1]

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 .

Thanks for your help.

[#2]

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'?

[swuehl]

Maybe like

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

Dates in Set Analysis

[swuehl]

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.

[#5]

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.

[sunny_talwar]

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';

[swuehl · Accepted solution]

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/d...‌

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

LOAD

     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

[#8]

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

[#9]

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

[jakobjensen]

What if we want to use autocalendar in the filter i. e

sum({<[Expiration Date]={'>=orderdate.autocalandar.Date'} yourvalue) 

does not seem to work