How to Exclude year Date from Filter Pane

[FelipeK16]

Hello friends, 

I have been struggling with how to exclude a Year from the filter pane with no success, 

As I attach on the screenshot(179), the year 2021 must not be shown, 

I have tried a if condition but it doesn't works, 

As:

=if(Match(DateField,'2022','2023'),DateField)

Another idea is 

=Aggr(Only({1<year = {2022},{2023}>} DateField), DateField)

Thank you,

[Chris-Jones]

If you're familiar with the data load editor, you could create a table of the years you want to include in your filter pane and use the year field from that table (YearFilter from my example below) as the dimension of your filter pane. The code might look something like this:

// ** years to include in filter pane **
FilterPaneYears:
LOAD
     RowID(),
     DateField AS YearFilter
RESIDENT ExampleTable
WHERE Year(DateField) IN (2022, 2023);

You use RESIDENT to reference a data table that has already been loaded into your app. The RowID() function just generates a key for the table and the DateField is just an assumption of what your date field might be named based on what you've provided in your initial post. ExampleTable is just the table you're using for your analysis (what you're using to reference data from 2022 and 2023). Hope that helps!

[BrunPierre]

If(Year(DateField)>'2021',Year(DateField))

[FelipeK16]

Hi Brun this is working, let me give you some more context, when it is excluding the Year 2021, I have a condition on the addons of the KPI that pushes the user to select a year, so when the user is not selecting any year a message is popping out from the KPI as "Please Select a Year"

for that purpose on the addon section of the KPI I have used 

GetSelectedCount([GSDP.termstartdate_vp.autoCalendar.Year])

Now, 

When I select a X year on the filter pane, Qliksense is not reading the condition, 

Thinking that I should replace the field on GetSelectedCount([GSDP.termstartdate_vp.autoCalendar.Year]) for 

GetSelectedCount(If(Year([GSDP.termstartdate_vp.autoCalendar.Year])>'2021',Year([GSDP.termstartdate_vp.autoCalendar.Year])))

Should fix the error, due to I am telling qliksense to read the entire formula first to show the data after selecting the year but this is not happening, do you have any clue why is this?

I attach a screenshot(181) that can give you more context

Thank you, 

[FelipeK16]

Hi Chris thanks for your help

I have made some syntax changes for qliksense

[FilterPaneYears]:
LOAD
RowNo(),
GSDP.termstartdate_vp AS YearFilter

RESIDENT [BrandConnect_park_info_testUS]

Where [GSDP.termstartdate_vp]=('2022') AND ('2023');

But is not working, do you see anything I am missing?

It is loading without any problem, GSDP.termstartdate_vp is my date field name, the RowID which is from Oracle I have changed to RowNo 

RowNo( ),Returns an integer for the position of the current row in the resulting QlikView internal table.  RecNo () , which counts the records in the table of pure data, RowNo () function does not count records excluded by clause where  and is not reset when a pure table Concatenation with another.

I attach the Data Island created from this solution on the screenshot(184) below,

Thanks,

[Chris-Jones]

Hey Felipe,

It looks like your WHERE clause might be looking for years that are both 2022 and 2023. You'll want something like 2022 or 2023. Try this line of code again using IN with your data:

WHERE [GSDP.termstartdate_vp] IN ('2022', '2023');

That should look for years that are 2022 or 2023.

Also, if you already have a key field from your table BrandConnect_park_info_testUS, you can pull that over as your first field and skip the RowNo() function.

[FelipeK16]

Chris, this is the your code with the IN 

Qualify *;

Unqualify VIN_;

[GSDPYearFilter]:
LOAD
[VIN_],
[GSDP.termstartdate_vp] AS YearFilter

RESIDENT [BrandConnect_park_info_testUS]

Where match([GSDP.termstartdate_vp],'2022','2023');

Qliksense instead of using IN uses match, 

I did use the VIN_ which is the key and the tables are associated, 

I did use the unqualify keyword to associate this new table, 

and the Qualify to exclude GSDP.termstartdate_vp and YearFilter from the association, 

What have happened?

It has created the table:

[GSDPYearFilter.YearFilter] which I have used on the filter pane to look for the result of the script which should be 2022, 2023 as segment options, but instead of doing that there is no segment options, nor 2022, or 2023, 

And If I use again on the filterpane the field GSDP.termstartdate_vp shows back from 2021, 2022, and 2023 segment years options.

I attach the result of this from the Data Model viewer on the screenshot(186) I understand this as the new field is not taking the dates from the original date field GSDP.termstartdate_vp

screenshot(188) shows the result on the filterpane when using the new field [GSDPYearFilter.YearFilter]

I am very grateful of your help

Note: I have added the new field to the calendar so that I can use it on the filterpane

[FelipeK16 · Accepted solution]

I found a solution

It is

=aggr(Only({<GSDP.termstartdate_vp.autoCalendar.Year = {">=$(=YearStart(Today()))"}>}
[GSDP.termstartdate_vp.autoCalendar.Year])
,GSDP.termstartdate_vp.autoCalendar.Year)

Now it only shows the Years from 2022 and ahead of it.