Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,
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)
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
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
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
Thanks for pointing me in the right direction, Pablo! Time to study!
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