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

Creating filter for month and year from a single date field


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:

mês.JPG

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,

1 Solution

Accepted Solutions
sunny_talwar

Try this:

LOAD *,

          Year(Mes) as Year,

          Month(Mes) as Month;

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

          yourotherfields

FROM Source

View solution in original post

3 Replies
sunny_talwar

Try this:

LOAD *,

          Year(Mes) as Year,

          Month(Mes) as Month;

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

          yourotherfields

FROM Source

swuehl
MVP
MVP

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)

or

=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#().

Not applicable
Author

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