Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stats101
Partner - Contributor III
Partner - Contributor III

Last x days filter not working

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable

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;

View solution in original post

7 Replies
lakshmikandh
Specialist II
Specialist II

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
Partner - Champion III
Partner - Champion III

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],

....

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
fashid
Specialist
Specialist

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
Creator III
Creator III

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
Not applicable

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Creator II
Partner - Creator II

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();