Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 .

Thanks for your help.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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):

2016-09-09 08_56_34-Qlik Sense Desktop.png

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

View solution in original post

9 Replies
Not applicable
Author

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
MVP
MVP

Maybe like

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



Dates in Set Analysis

swuehl
MVP
MVP

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.

Not applicable
Author

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
MVP
MVP

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):

2016-09-09 08_56_34-Qlik Sense Desktop.png

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

Not applicable
Author

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

Not applicable
Author

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

jakobjensen
Contributor II
Contributor II

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

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

does not seem to work