Re: Date filters - Qlik Community

Anonymous · ‎2018-04-30

Hi,

I'm very new to Qlik Sense (I'm using Qlik Sense Desktop)

I have data with Dates columns with the format of dd/m/yyyy hh:mm (e.g. 23/1/2016 23:25)

Is there a way with not much scripting to add filters on this column that will show only -" Today's data", "This week's data", "This year's data" ?

I was searching the community and saw some syntax that should be relevant but couldn't incorporate it successfully .

I tried to add a filter object with the syntax =[my date column] =Date(Today()-30) and so but it didnt seems to work

Thanks!

Anonymous · ‎2018-04-30

Hi Yohay,

What exactly is it that you're looking to do?

If you're just looking to create filters in the front end of the app. A super easy way to do this would be to create 3 filter panes on your sheet:

Floor(Date) ; this will return just the date field ex. 2018/4/30
Week(Date) ; this will return the week which the date is in
Year(Date) ; this will return the year

And then the user looking at the app can simply select the year, week or date they wish to see.

Anonymous · ‎2018-05-01

Hi Serina,

Thanks.

What Im looking for is to add a filter "Today" that will show data of today, "This month" will show data of the current month and "Year" (same logic)

I'm not sure if I can do this with the filter object or I will need to create a button (I dont know how to create an action button)

adj29block · ‎2018-05-01

Hello,
Your date format is DD / MM / YYYY hh: mn: ss

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

If your date field starts in 2016 you have to create MinDate and MaxDate
with Makedate
Example
LET vMinDate = Num (Makedate (2015,1,1));
LET vMaxDate = Num (Makedate (2017, 12,31));
You can also use either autocalendar from QS or MasterCalendar

adj29block · ‎2018-05-01

In Main add at the end the MasterCalendar script and paste this

LET vMinDate = Num(Makedate(2015,1,1));

LET vMaxDate = Num(Makedate(2017,12,31));

ChampDate:

LOAD

date($(vMinDate)+IterNo()-1) as TempDate

AUTOGENERATE (1)

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

Calendrier:

LOAD

TempDate as OrderDate,

Year(TempDate) as Year,

'Q ' & Num(Ceil(Month(TempDate)/3),'(ROM)0') as Quarter,

Month(TempDate) as Month,

Dual(Year(TempDate)&'-Q'&Num(Ceil(Num(Month(TempDate))/3)),QuarterStart(TempDate)) AS [YearQuarter],

Dual(Year(TempDate)&'-'&Month(TempDate),Monthstart(TempDate)) AS YearMonth,

Dual(Month(TempDate)&'-'&Year(TempDate),MonthStart(TempDate)) As MonthYear,

MonthEnd(TempDate)as MonthEnd,

MonthStart(TempDate) as MonthStart,

Week(TempDate) as Week,

Weekday(TempDate) as WeekDay,

Day(TempDate) as Day

RESIDENT ChampDate;

DROP TABLE ChampDate;

Anonymous · ‎2018-05-01

Hi

Thanks !

Where can I define and use these variables?

Anonymous · ‎2018-05-01

Well, that really depends on what you're looking to build. I'm still a little confused as to what it is that you want to do.

You can create filter panes and simply have the user viewing the app make selections in the filter pane to view the year, month, week, or data that they want to view. And these selections that the user makes will be apply throughout the entire Qlik Sense app/dashboard. This would be the easiest if you don't want to do a lot of scripting.

Or if you're saying that you only want them to see data for this month and they cannot access data for other months, then you can do so using set analysis. However, you will need to apply set analysis to each individual visualization/item that you have in the app. You can read more about set analysis here: https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-an...

Also, I'd suggest you take a look at the Qlik Sense on-boarding page: https://www.qlik.com/us/onboarding‌. You can find tutorial videos and learning modules there. There is also some great beginners' tutorials here as well: https://help.qlik.com/en-US/sense/April2018/Content/Tutorials.htm

I hope that helps!

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Anonymous · ‎2018-05-01

You can create variables in the back end using the Data Load Editor: https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/work-with-variables-in-...

Or in the front end in Edit Mode. The Variables button is located on the bottom left of the properties pane. https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Sheets/variables-dialog.htm

Anonymous · ‎2018-05-03

Thanks a lot. I will try to learn through the process

Anonymous · ‎2018-05-03

Thank you very much , I will go over these and hopefully will succeed setting this up

Re: Date filters