Calculated Dimension not working / Error in Calculated dimension

[skzwane01]

Good day,

I am struggling with creating a calculated dimension.

I have an expression which works when done on textbox however when made a calculated dimension it does not work.

Please see below:

expression:

=if(Date(Date#((Effective_Month_Key), 'YYYYMM'), 'YYYYMM') - Date(Date#(Date(max(Last_Hire_Dte)), 'YYYY/MM/DD'), 'YYYYMM') >=90 and Staff_Type_Descr ='Temporary','Permanent',Staff_Type_Descr)

This works fine on the textbox.

When done as a calculated dimension it does not work. Please see below 

Please assist. How can I resolve ?

Kind Regards,

[Kushal_Chawda · Accepted solution]

you need aggr function to work your calculated dimension correctly

=aggr(if(Date(Date#((Effective_Month_Key), 'YYYYMM'), 'YYYYMM') - Date(Date#(Date(max(Last_Hire_Dte)), 'YYYY/MM/DD'), 'YYYYMM') >=90 and Staff_Type_Descr ='Temporary','Permanent',Staff_Type_Descr), Staff_Type_Descr)