Date filter

[Venamma23]

Hi,

I'm trying to create a date filter as below, which gives us consolidated data for weeks when we select the filter as weeks same as for months and year .

Expectation is creating a single field which will have daily and weekly ,monthly options so that we can create a  filter with drop down option

And the data should also be consolidated according to the filter selection.

I tried using date picker extension but somehow its giving me filtering options in different way not like what I'm expecting.

 So please help me with this..

Regards,

Venamma

[florentina_doga]

create a drill down group with year, month, week, day

[florentina_doga]

master items - dimensions - create new

[Venamma23 · Accepted solution]

Hi,

I tried creating the drill down dimension but its showing the options like all the months(Jan, Feb, Mar...) present in my data set.

I'm looking for something like a filter with drop down options (daily,weekly,monthly).

If I click on

Daily -it should show me daily data  in my pivot table(date is a column in my pivot)

Weekly - It should show me consolidated weekly data which means from (Monday to Friday)as a single week

Monthly - It should show me consolidated data for all the months in my data set

Kindly checkout the attachment.

Regards,

Venamma

[Vegar · Accepted solution]

You can do it with an inline table and a calculated dimension like in my attached example.

In short the script looks like this:

LOAD
    OrderID,
    OrderDate as %OrderDate,
    Price
FROM [lib://QlikCommunity/2.QVD/Orders.qvd] (qvd)
;

For each _date in fieldvaluelist('%OrderDate')
Calendar:
LOAD 
    '$(_date)' as %OrderDate,
    DayName('$(_date)' ) as Date,
    Month('$(_date)' ) as Month,
    Year('$(_date)' ) as Year
 AutoGenerate 1;
next

Filter:
LOAD * inline [
Period Filter, PeriodFilterField
Daily, Date
Monthly, Month
Yearly, Year
];

And my dimension look like this:

=$(=maxstring(PeriodFilterField))

[Venamma23]

It's really helpful, Thanks a lot 🙂