Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return a month over mm/dd/yyyy not working

Hello,

I need some help. I have a list of Travel and Expense reports (Air tickets, Hotel, Train, etc.). I'm having a fundamental problem. One of the fields is [Travel Start Date] that has a Time format MM/DD/YYYY. Not all of the lines of the report has that information, and I will have more than one line or ticket the same starting date [Travel Start Date].

I have a few fundamental problem. I can't create a single Filter Pane using that field and extract only the Year of that list of values (2015, 2016, 2017).

Thanks a lot in advance.

5 Replies
Anil_Babu_Samineni

Would you provide sample data and then show your Expected result to see

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vinieme12
Champion III
Champion III

If you want to do it load

Load *

Year(TravelDate) as TravelYear

From YourTable;

If you want to do directly in listbox then

Aggr(only({<TravelDate>} Year(TravelDate) ) , TravelDate)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

you might also want to read about Master calendars

Use of Master Calendar

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
rahulpawarb
Specialist III
Specialist III

Hello Ignacio,

I believe that problem is records without [Travel Start Date] will be excluded from the report if you select a year in the filter. It this is the case then use below expression in filter object.

=Alt(Year(Date#([Travel Start Date],'MM/DD/YYYY')),Year(Today()))

NOTE: I selected [Travel Start Date] field from inline table that's why I added Date# function to interpret it as a date. If my records don't have any [Travel Start Date] value then I am populating Current Year value(You can define any value of your choice.

Thank you!

Rahul

Not applicable
Author

Thanks a lot for your help.