Today, Yesterday, This Week, etc, all in one field

[lawrenceiow]

I'm not sure if this is possible but I'm often surprised by what QlikView can do so I'll ask the question just in case.

I'd like to have a Listbox which contains date periods as words. For example, selecting the words "This Week" from the listbox will filter all the dates in this week. Likewise, selecting "This Month" or "Last Month" will select the appropriate date range.

Currently I have different listboxes for Year, Month, etc and the user makes numerous clicks in order to get the date range they require. I am hoping to reduce the number of clicks the user has to make and also reduce the space that the many listboxes currently occupy.

Thank you for any help you can give.

[sunny_talwar · Accepted solution]

You can definitely create something like this using a link table:

LinkTable:

LOAD Date

          'Today' as Flag

FROM FactTable

Where Date = Today();

Concatenate (LinkTable)

LOAD Date,

          'Yesterday' as Flag

FROM FactTable

Where Date = Today() - 1;

Concatenate (LinkTable)

LOAD Date,

          'This Week' as Flag

FROM FactTable

Where Date >= WeekStart(Today());

[Gysbert_Wassenaar]

Create a table in the script with records that tag the dates with the period descriptions you want:

Periods:

LOAD Today() as MyDate, 'Today' as Period Autogenerate 1;

LOAD Today() -1 as MyDate, 'Yesterday' as Period Autogenerate 1;

LOAD Today() + 1 - RecNo() as MyDate, 'This Week' as Period Autogenerate 7;

LOAD Today() + 1 - RecNo() as MyDate, 'This Month' as Period Autogenerate Day(Today());

etc...

[vinieme12]

Create your own period flags as required

Period Presets: Compare Periods on the fly

[lawrenceiow]

Doh! Of course, it's obvious now I look at your answer, I don't know why I didn't think of it this way.

Thank you very much for your answer stalwar1‌

[lawrenceiow]

I like your answer gwassenaar‌ except I don't understand how it works but it certainly does. I expect it's simple, I'll have to look up Autogenerate.

I'd like to mark your answer as correct too, as it works but I only get to pick one.

Thanks for your help.

[lawrenceiow]

Thank you vinieme12‌, this will be useful later but for now the end users are not comparing data.

Regards

[vinieme12]

this is not specifically for comparing, you can use it as filters too!