Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Help with expression wildcard on a date - only show year = 2015

Hi, I'm trying to show only data from 2015.  Here is the expression I wrote that isn't working:

Sum({$<MONTH = {'2015*'}>} POPULATION)

I've tried different configurations of this, but everyone returns a blank table.

MONTH field in my input table is formatted as dd.mm.yyyy
In my table, I selected MonthYear

I've tried putting the asterisk before 2015 and it doesn't work.  I've also tried showing only march 01, by writing  {'01.03*'} and that doesn't work, either.

 

 

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Help with expression wildcard on a date - only show year = 2015

= sum({<MONTH= {">=$(=Date#('01.01.2015','dd.mm.yyyy')) <=$(=Date#('31.12.2015','dd.mm.yyyy'))"}> } POPULATION)

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

View solution in original post

9 Replies
Highlighted
Specialist III
Specialist III

Re: Help with expression wildcard on a date - only show year = 2015

Maye be :

= sum({<MONTH= {">=$(=Date#('01.01.2015','dd.mm.yyyy'))"}> } POPULATION)

you can also change in load Script :

SET DateFormat='DD.MM.YYYY';

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Contributor III
Contributor III

Re: Help with expression wildcard on a date - only show year = 2015

Thanks for replying.

Qlik accepts that as an acceptable expression, but it doesn't actually filter anything.  It still shows the full date range, including  data from 2016, 2017, 2018, etc.

Highlighted
Specialist III
Specialist III

Re: Help with expression wildcard on a date - only show year = 2015

= sum({<MONTH= {">=$(=Date#('01.01.2015','dd.mm.yyyy')) <=$(=Date#('31.12.2015','dd.mm.yyyy'))"}> } POPULATION)

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Help with expression wildcard on a date - only show year = 2015

Ahhh, okay.  I wasn't reading the ">" as a greater than symbol.  I thought it was a different version of the angle brackets that Qlik uses in its expressions.  Also, my data starts at 2015, so I wasn't seeing any change.

Thanks.  I may have a follow up question about the syntax.

Highlighted
Partner
Partner

Re: Help with expression wildcard on a date - only show year = 2015

You should adjust your modifier to double quotes like this:

Sum({$<MONTH = {"2015*"}>} POPULATION)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

Re: Help with expression wildcard on a date - only show year = 2015

That still doesn't work. 

The above poster's  solution works.  I'm assuming it has something to do with MONTH being formatted as a date and not a string or numberic.

Highlighted
Specialist III
Specialist III

Re: Help with expression wildcard on a date - only show year = 2015

can you share a sample data if @Vegar 's solution not working for you

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Contributor III
Contributor III

Re: Help with expression wildcard on a date - only show year = 2015

Okay, I got his to work.  I just needed to use the double quotes and put the asterisk BEFORE the year.  Anyway, can you explain how this works, and why some of these expressions work and some don't?  I've included the data below.

Sum({<MONTH= {">=$(=Date#('01.01.2016','dd.mm.yyyy')) "}> } POPULATION)

Works:  Gives me everything great or equal to 2016.


Sum({<MONTH= {"=$(=Date#('01.01.2016','dd.mm.yyyy')) "}> } POPULATION)

Does not work:  I was expecting to get everything equal to Jan-1, 2016, instead I get an empty table.


Sum({<MONTH= {">=$(=Date#('*.2016','dd.mm.yyyy')) "}> } POPULATION)

Does not work:  I was expecting everything greater and equal to 2016 using the wildcard. Instead I got an empty table.

 

Qlik Date.jpg

Highlighted
Contributor III
Contributor III

Re: Help with expression wildcard on a date - only show year = 2015

This works with the asterisk in front:

Sum({$<MONTH = {"*2015"}>} POPULATION)