Dynamic Filter to filter by last /previous years

[Qlikuser09]

=Year =Max(Year) works fine in filtering out max year

, but =Year =Max(Year) -1 or Year = {"$(=Max(Year) - 3)"} not filtering out anything giving me null values in Year filter

[MatheusC · Accepted solution]

@Qlikuser09 

see it like this:

=FieldYear=$(=max(FieldYear))  -> current
=FieldYear=$(=max(FieldYear))-1  -> previous

or

=FieldYear=Year(Today()) -> currect
=FieldYear=Year(Today())-1 -> previous



- Matheus

[Qlikuser09]

thanks, the above formula (Year=Year(Today())-1)worked well for previous year to filter by . but i have data like below i wanted to filter by last three days or from current calendar date at midnight. what formula can be used in this case to filter by 

{< DateField= {"=DateField>= Date(Today() - 2) and Datefield<= Date(Today())"} >}  or 

=[Datefield]<=$(=max(DateField))-1  is not working and rendering any results 

[MatheusC]

Cool, try this

=FieldDate>=today()-2 and FieldDate<today() -> Does not include today's date

=FieldDate>=today()-2 and FieldDate<=today() -> includes today's date


- Matheus

[Qlikuser09]

currently the "field Date" is not in the date format , is there way we could filter by the latest 3 dates without having to change the data model. (it works with only when filtered by year , but not with date or month)

[MatheusC]

If you can incorporate it into the script by creating a new field, that would certainly be the best way. However, you can also insert this formatting into your search filter

=Date(Floor(TimeStamp#(FieldDateTime, 'DD/MM/YYYY hh:mm:ss TT')), 'DD/MM/YYYY')

Regards