topic Creating filter for month and year from a single date field in QlikView

Creating filter for month and year from a single date field

Thu, 05 Nov 2015 21:17:26 GMT

Hello everyone,

I am working in a project where the date of my data base have only the month and year -> Oct/15

I am traying to create the distinct filters, one for month and another for year.

When i choose the field of the Date, it brings all the lines from the field into the table, as you can see below:

How can i create this filter showing only one option of October?

And how can i create another filter of year, showing only one option of 2015?

Thanks,

Re: Creating filter for month and year from a single date field

sunny_talwar — Thu, 05 Nov 2015 21:19:19 GMT

Try this:

LOAD *,

Year(Mes) as Year,

Month(Mes) as Month;

LOAD Date#(Mes, 'MM/YYYY') as Mes

yourotherfields

FROM Source

Re: Creating filter for month and year from a single date field

swuehl — Thu, 05 Nov 2015 22:07:59 GMT

What Sunny suggested should work.

A list box shows only distinct values. When you see apparently duplicate lines, there must be a difference. Either the field values are pure text values, then they might differ in some 'invisible' chars, like spaces.

You can create an expression in your list box

=len(Mes)

to see if the length is different for the lines.

Or the values have an underlying numeric representation that differ (like a QV date).

That's what I assume, that you have date or timestamp values formatted as Month/Year. Add an expression to your list box to check this:

=num(Mes)

=Timestamp(Mes)

If my assumption is correct, it may be worth considering keeping the more granular value in your data model. And if the values show a numeric representation, you can directly use QV date functions like month() and year() without the need to interpret the value using Date#().

Re: Creating filter for month and year from a single date field

Tue, 05 Sep 2017 14:26:04 GMT

You can't unless you have developer access and know scripting. The presentation has severe limitations for end users.