Date filter with greather than etc.

[schmidtj]

Hi,

i have two filter panes, one each for month and week.
The data behind are date values formated with Monthname() and Weekname().
In the filter i can select ranges when i go to day-level, for example >=01.05.2022 works fine.

However i would like to do that in relation to the actual date format, so in other words i would like this to work:
>=Jan. 2021 or >=2022/16.

That should be possible somehow, shoudlnt it?
I tried dual() but cant get it to work with this either.

[Andrei_Cusnir]

Hello,

If my understanding is correct, you would like to compare something like that:

• Feb. 2021 >= Jan. 2021 = TRUE

Or

• 2022/16 >= 2022/10 = FALSE

As it is, this can't be done for the reason that both are strings and thus they need to be converted back to dates so the comparison is accurate. In general the idea is to compare these:

• ConvertToDate('Feb. 2021') >= ConvertToDate('Jan. 2021') = TRUE

Or

• ConvertToDate('2022/16') >= ConvertToDate('2022/10') = FALSE

However the function ConvertToDate() doesn't exists in Qlik Sense as described above, which means that you would have to make the conversion by using a combination of multiple other functions. Here are the examples:

NOTE: Please keep in mind that the following information might not be a 100% solution to your use case scenario but hopefully it will give you a starting point to progress further!

Converting 'Feb. 2021' format:

MakeDate(
    SubField(DATE_FIELD, '. ', 2), 
    Match(SubField(DATE_FIELD, '. ', 1),'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ), 
    1
  ) 

This expression with create a date by splitting the string 'Feb. 2021' into parts 'Feb' and '2021'. Then it will use the '2021' part as year and with the function Match(), it will use the part 'Feb' to determine which month it is as numeric value. Finally the number 1 indicates the first day of the month.

Converting '2022/16' format:

MonthStart(MakeWeekDate(SubField(DATE_FIELD, '/', 1), SubField(DATE_FIELD, '/', 2)))

This expression will create a date by converting the string and with the MonthStart() function, it will make sure that the date starts from the first day of the month.

How is this helpful? Now you can make the comparisons. You can use the example below to get the logic behind the expressions and modify them to cover your use case scenario:

1. I have created the following variables:

2. I have loaded dates with the format that you have specified:

Now for the 3 column I have the following measure expression:

If(
  MakeDate(
    SubField(Dates2, '. ', 2), 
    Match(SubField(Dates2, '. ', 1),'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ), 
    1
  ) 
  >= 
  MakeDate(
    SubField('$(vMonthYear)', '. ', 2), 
    Match(SubField('$(vMonthYear)', '. ', 1), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ), 
    1
  ), 
  'Yes', 
  'No'
)

As you can see, it will convert the date of the dataset that I am currently reading to a date value and it will do the same for the date string saved in the variable. This will ensure that we are comparing dates instead of strings and thus the result is correct.

3. The same is done for the other string format:

With the 3rd column having the following measure expression:

If(
  MonthStart(MakeWeekDate(SubField(Dates3, '/', 1), SubField(Dates3, '/', 2)))
  >= 
  MonthStart(MakeWeekDate(SubField('$(vWeekNum)', '/', 1), SubField('$(vWeekNum)', '/', 2)))
  , 
  'Yes', 
  'No'
)

As you can see we first convert the dataset's value to date value and we are doing the same for the date string value stored in the variable. Only after that we are comparing to see if the one is grater/equal to the other or not. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[schmidtj]

Hi,

thanks for the reply.

Well when i read the documentation for weekname() and monthname() it tells me that it returns a dual with a timestamp as the numeric value. When i enter >=Jan. 2021 in the filter pane this is a string yes, but internally i would have hoped qlik is using the corresponding date-value.
When i enter >=01.01.21 it is a string too but it gives me the correct month-values in the filter pane.
Internally qlik is converting that string into date and make the comparison.
I dont understand why qlik doesnt do that for >=Jan. 2021 since the values in the filterpane are formatted accordingly.

[Andrei_Cusnir]

Hello,

I think I might know why you are facing the issue and here is my hypothesis. 

1. I have the following dataset:

2. After that I have tested simple comparisons:

As you can see the ">=" is working since it returns that 2022/01 >= 2022/11 is FALSE and for 2022/11 >= 2022/01 is TRUE, which is correct and from that we know that the generated values are indeed duals, since it has the string representation and integer to preform the calculation. 

3. However, reading through the description of the initial post again, I understand that you have created the filter panes by only having the date field in the dataset, like this:

Is this correct? 

In that case, my recommendation would be to create the additional fields MonthName and WeekName within the dataset itself. Either through Data load editor or by adding new calculated dimensions through Data manager (Depends on how you have loaded your data). After that, create the Filter panes using directly the newly generated values instead of creating the values in the dimension itself. This could probably help you resolve the issue.

In case you are still facing the issue after that, please share here a sample app with fake data (Fake dates and values), where the comparison ">=" doesn't work properly, so we could investigate the issue further.

[schmidtj]

Hi Andrei,

thanks a lot for your reply.
Well i already did create the fields in my datascript which i am filtering.

This is my month field in the script: Monthname("Monat: Ausgabe") AS "Monat (Text)"
I am using "Monat (Text)" directly in my filter widget.

>=01.01.22 works fine, >=2022/01 does not also not >=Feb. 2022  for example which i would have expected to work.

[Andrei_Cusnir]

Hello,

Maybe if you can share here a sample app with fake date values, we will be able to see how the data is exactly loaded and processed and we can try the same filter strings that you are trying to see how the app behaves. Please also share the exact version of Qlik Sense that you are using (e.g. Qlik Sense May 2021 SR3).

Note: Please avoid sharing sensitive data. You can create an excel with fake dates and then create a new app where you will load the fake dates. As soon as you manage to reproduce the issue with the fake EXCEL's data, please share here both the EXCEL file and the sample app.