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

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
pablolabbe
Luminary Alumni
Luminary Alumni

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

View solution in original post

6 Replies
Thiago_Justen_

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
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
pablolabbe
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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

pablolabbe
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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

pablolabbe
Luminary Alumni
Luminary Alumni

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