From Date and To Date from single source

[mhmmd_srf]

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

[shraddha_g]

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]

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]

Have a look at

Subroutine to Create Data Model for From/To Date Selection

[mhmmd_srf]

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]

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.