Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mhmmd_srf
Not applicable

From Date and To Date from single source

Hi All,

I have excel as source of dashboard. Excel contains only one date Field called Date Time Arrived in MM/DD/YYYY hh:mm:ss format.

I want to make two filter one is from date another is to date as user want to select a date range.

How to implement this. Please help.

Thanks,

Sarif

5 Replies
shraddha_g
Not applicable

Re: From Date and To Date from single source

Create master calendar in script with your date field and Try extensions mentioned in below link

https://www.qlikcommunity.org/thread/235102

It might be helpful.

dimitar_angelov
Not applicable

Re: From Date and To Date from single source

Hello Mohammad,

Keep in mind there is almost 100% better solution

If your data is not big it should work smooth. Here is my suggestion to you.

1. Create 2 calendars. 1 MUST be conected to your data, the other can be island.

2. Display them in the same format in the layout and label one "From" the other one "To".

3. Create 2 variables 1 will represent the "From" state and will be calculated using the selections from the FromCalendar, the second one will represent the "To" state and will be calculated using the selections from them "ToCalendar".

4. Write a set expression and use both variables as frame to your calculation.

If you your data is less than 100 mln rows this is easy solution and will not be hard to execute.

Regards,

Dimitar

swuehl
Not applicable

Re: From Date and To Date from single source

mhmmd_srf
Not applicable

Re: From Date and To Date from single source

Hello Dimitar,

I tried your logic.

Created two data island. Both are disconnected.

Table1: Table name is Date_From, Field name is From_Date

Table2: Table name is Date_To, Field name is To_Date

two variable:

v_FromDate= From_Date

v_ToDate= To_Date

But my expression is not working. And one more thing is it really required to be connected for one table?

Please help and suggest.

Thanks,

Sarif

dimitar_angelov
Not applicable

Re: From Date and To Date from single source

Hello Mohammad,

This is the expression you need:

sum({<Date={'>=$(vFrom)<=$(vTo)'}>}Records)

*

     vTo =Max(To.Date)

     vFrom =Min(From.Date)

The following set expression will help you filter from and to.

Note:

1.Date format must be the same in the fields you are comparing in order for this to work. If one of your dates is DD/MM/YYYY and other is Jan 2016 it will not work.

2. The Date field in the set expression MUST be related to your data so it can actually filter it. I would recomend using only 1 data island and the other can be your main calendar conected to your data. 2 data island is also possible but you will need a 'Date' field related to your information and is also not a good practice.