topic Date Range filters in QlikView

Date Range filters

itcontracts — Fri, 26 Jul 2024 19:55:24 GMT

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.

Re: Date Range filters

BrunPierre — Sun, 28 Jul 2024 21:07:35 GMT

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')))

Re: Date Range filters

itcontracts — Mon, 29 Jul 2024 12:32:06 GMT

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