Last x days filter not working

[stats101]

I'm using the following query to generate a filter to restrict data to the selected number of days:

IF(TODAY()-[Creation Date]<=1,'Last 1 day',
    IF(TODAY()-[Creation Date]<=3,'Last 3 days',
        IF(TODAY()-[Creation Date]<=7,'Last 7 days',
            IF(TODAY()-[Creation Date]<=14,'Last 14 days',
                IF(TODAY()-[Creation Date]<=30,'Last 30 days',
    )))))

However, it isn't performing as expected. For instance, if I select 'Last 7 days' it, only returns records between 3 to 7 days; I need to select the previous 2 selections to get the full 0 - 7 days I require.

[lakshmikandh]

Using this extension define a variable vDays and select type as Dropdown  GitHub - erikwett/qsVariable: Variable extension for Qlik Sense

define alternatives in that extension like this,

1,'Last 1 day', 

3,'Last 3 days', 

7,'Last 7 days', 

14,'Last 14 days', 

30,'Last 30 days', 

and apply condition TODAY()-[Creation Date]<=vDays in your charts

This will filter the value the way you want.

[jonathandienst]

Well you can only have a single value in one field, so if an item is already assigned to 'Last 3 days', it cannot also be assigned to 'Last 7 days'. You will need to create a separate field for each of the time windows:

If(Today() - [Creation Date] <= 1, 1, 0) as [Last 1 day],

If(Today() - [Creation Date] <= 3, 1, 0) as [Last 3 days],

If(Today() - [Creation Date] <= 7, 1, 0) as [Last 7 days],

....

[fashid]

Hi,

Jonathan is right or simply make a selection on all the 3 buckets ,'Last 1 day','Last 3 days','Last 7 days' to get your last 7 days output.

Regards,

Nadeem

[thomaslg_wq]

You could maybe use this script, but you have to access the script

Your_Initial_Data:

LOAD

Field1,

Field2,

TODAY()-[Creation Date] as Days                          // Add this calculation

FROM Your_Initial_Source;

Intervalmatch(Days)

LOAD * INLINE [

From, To, LastDays

0, 1, Last 1 day

0, 3, Last 3 days

0, 7, last 7 days

0, 14, last 14 days

0, 30, last 30 days

];

Drop fields From, To;

[Anonymous · Accepted solution]

Assuming you have a Calendar table with the Creation Date field.  In this case, it makes sense to create a separate "filter" table:

FilterTable:

LOAD DISTINCT

[Creation Date],

'All' as Filter

RESIDENT Calendar;

CONCATENATE (FilterTable)

LOAD DISTINCT

[Creation Date],

'Last 1 day' as Filter

RESIDENT Calendar

WHERE TODAY()-[Creation Date]<=1;

CONCATENATE (FilterTable)

LOAD DISTINCT

[Creation Date],

'Last 3 days' as Filter

RESIDENT Calendar

WHERE TODAY()-[Creation Date]<=3;

.....

CONCATENATE (FilterTable)

LOAD DISTINCT

[Creation Date],

'Last 30 days' as Filter

RESIDENT Calendar

WHERE TODAY()-[Creation Date]<=30;

[stats101]

I was trying to avoid that approach as the comparison is contingent on the data being refreshed daily. i.e. the last x day would be relative to the refresh date.

[vishalarote]

Try this, this works.

e.g.

FilterTable:

LOAD DISTINCT

Date,

'All' as Filter

RESIDENT Data1;

CONCATENATE (FilterTable)

LOAD DISTINCT

Date,

'Last 1 day' as Filter

RESIDENT Data1

WHERE Date>=Daystart(TODAY(),-1) and Date<today();

CONCATENATE (FilterTable)

LOAD DISTINCT

Date,

'Last 3 days' as Filter

RESIDENT Data1

WHERE Date>=Daystart(TODAY(),-3) and Date<today();

CONCATENATE (FilterTable)

LOAD DISTINCT

Date,

'Last 30 days' as Filter

RESIDENT Data1

WHERE Date>=Daystart(TODAY(),-30) and Date<today();