Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mick_rovenstine
New Contributor II

Filter on a dimension from the master item list

I have created a dimension in the Master item list that returns a "year" as a function of a [date field] and a [length field] in months.

e.g. Master item 'Year' is defined as the function: year(addmonths([date field], [length field])

Subsequently when I use 'Year' as a dimension in a visualization and try to filter on it, I expect to see all of the records associated with a specific year, but instead, the filter associates my selection of a year to the selection of the corresponding [date field] and [length field] associated with my filter selection.

e.g.  I select '2013' in a filter and expect to see all the records with the calculated 'Year' of 2013. 

Instead it filters on [date field] and [length field] for all the records in 2013 and the result is records that contain any combination of those fields resulting in records with a variety of 'Year' outcomes.

Is there a way to filter only on a function's return value in the Master item list instead of the fields from which it was calculated?

Thanks in advance for any help!

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Filter on a dimension from the master item list

Mick,

  Unfortunately you can't create a new calculated field using fields from different tables. Your case need some extra steps to join the data between these two tables.

  My suggestion is you study about advanced scripting using join or perhaps applymap.

  Take a look in this help section to learn about fata modeling best practices.  https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/DataModeling/best-practices-d...

Pablo

6 Replies
thiago_justen
Valued Contributor III

Re: Filter on a dimension from the master item list

Mick,

How your expressions handle year dimension?

For instance: You have Sum (Sales) in your graph and you want to make filters on it. What I'd suggest you:

Sum ({<Year={"$(=Num#(GetFieldSelections (Year)))"}>} Sales)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Luminary
Luminary

Re: Filter on a dimension from the master item list

Hi Mick,

If you have access to the Data Load Editor or Data Manager, I recommend you create a calculated field in the load step. After reload, you will have a new field like any other and you can use it as a Dimension with no issues.

Can I ask you why you need to create this Year Dimension in that way ?

Regards,

Pablo

mick_rovenstine
New Contributor II

Re: Filter on a dimension from the master item list

Pablo,

Thanks for the response.

My data are individual items of inventory, each with an [Acquisition Date] and an [Inventory Type].  Each [Inventory Type] has a [Replacement Frequency] in months mapped from an associated table.  I need to be able identify by year, which inventory items will need to be replaced in which future years.

Your approach makes sense, but I don't know how to access the associated table, Frequency, in the Data Load Manager.  Thanks for the help.

Mick

Luminary
Luminary

Re: Filter on a dimension from the master item list

Mick,

  Unfortunately you can't create a new calculated field using fields from different tables. Your case need some extra steps to join the data between these two tables.

  My suggestion is you study about advanced scripting using join or perhaps applymap.

  Take a look in this help section to learn about fata modeling best practices.  https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/DataModeling/best-practices-d...

Pablo

mick_rovenstine
New Contributor II

Re: Filter on a dimension from the master item list

Thanks for pointing me in the right direction, Pablo!  Time to study!

Luminary
Luminary

Re: Filter on a dimension from the master item list

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

Community Browser