topic Re: Creating a Calculated Dimension With Nested If Function, Based on Values on a Linked Table in QlikView

Creating a Calculated Dimension With Nested If Function, Based on Values on a Linked Table

bdickinson3 — Thu, 08 Feb 2024 16:57:53 GMT

I have been attempting to create a calculated dimension that calculates the values in a column to a table linked through an ID.

=if(Count([Employee.Level] = '01') >= 1 AND Count([Employee.Level] = '02') < 1 AND Count([Employee.Level]] = '03') < 1 AND Count([Employee.Level] = '04') < 1 AND Count[Employee.Level] = '05') < 1, 'New Hire', '1 Year+')

The expression editor says that the script is okay, but whenever I close out of it it claims it as an invalid dimension.

Re: Creating a Calculated Dimension With Nested If Function, Based on Values on a Linked Table

hic — Thu, 08 Feb 2024 17:05:02 GMT

The expression is OK as measure, but not as dimension. You cannot have aggregation functions in a dimension, unless you use Aggr().

So, the question is: Per WHAT do you want to calculate all these counts? Per department? Per month? Per Employee category? You need an internal iterator - a dimension in the Aggr(). If it is per Department, you should use:

=Aggr(

if( Count([Employee.Level] = '01') >= 1 AND

Count([Employee.Level] = '02') < 1 AND

Count([Employee.Level]] = '03') < 1 AND

Count([Employee.Level] = '04') < 1 AND

Count[Employee.Level] = '05') < 1,

'New Hire',

'1 Year+'

Department

)

Re: Creating a Calculated Dimension With Nested If Function, Based on Values on a Linked Table

bdickinson3 — Thu, 15 Feb 2024 13:36:49 GMT

Thank you. I'm afraid I have another problem though.

I wanted to use this expression as a filter pane with both options, but it's only showing "1 Year +" as an option. I wanted to be able to filter Incident IDs based on the highest-level employee involved in the incident, where Employees are linked together by similar Incident IDs (I realize I have to also include Incident_ID into the expression).

I appreciate any advice with this.