Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
itcontracts
Contributor
Contributor

Date Range filters

I initially tried achieving this through nested if, but it was not returning the desired results.

=if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-3)),day(today())),
  'Past Three Months', 
  if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-6)),day(today())),
  'Past Six Months',
    if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-12)),day(today())),
    'Past Year',
        if(WhenDate<MakeDate(year(Today()),Month(addmonths(today(),-12)),day(today())),
'Before a Year ago',
  '')
          )
       )
    )


I then saw a pick and match, but that doesn't work either.  


=Pick(
Match(WhenDate,'Three Months','Six Months','Year','Year Beyond'),
WhenDate<addmonths(today(),-3),
WhenDate<addmonths(today(),-6),
WhenDate<AddYears(today(),-1),
WhenDate>AddYears(today(),-1)
)

 Any suggestions on how to get this in a filter pane would be great.
Thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

How about this?

If(WhenDate >= AddMonths(Today(), -3), 'Past Three Months',
If(WhenDate >= AddMonths(Today(), -6), 'Past Six Months',
If(WhenDate >= AddYears(Today(), -1), 'Past Year','Before a Year Ago')))

View solution in original post

2 Replies
BrunPierre
Partner - Master
Partner - Master

How about this?

If(WhenDate >= AddMonths(Today(), -3), 'Past Three Months',
If(WhenDate >= AddMonths(Today(), -6), 'Past Six Months',
If(WhenDate >= AddYears(Today(), -1), 'Past Year','Before a Year Ago')))

itcontracts
Contributor
Contributor
Author

Apologies for the overcomplicated code as it was a long day.  I initially convert to the addmonths/addyears after this post.  This solution worked.