"if" Expression treated as a mesure by Qlik instead of dimension

[FernandaNava]

Hello, 

I have several columns with integer numbers representing ages. I have also a column for birthdate and another column with a reference date. I'm calculating an age using these two dates, and highlighting the values on the other columns that are lower than this calculated age. 

I tried doing this as a flag with an if statement but even though i don't get an expression error, after clicking applied I get "Invalid dimension". It seems to be treating it as a measure and so I'm unable to use it as a filter. 

If I use it as a condition for background color it works: 

if(Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#([Age1), cyan())

But I get "invalid dimension" when I try creating a master dimension:

=if(Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age1)

or Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age2)
or Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age3)
or Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age4), 'Y, 'N')

This happens even if I remove the OR conditions and try doing it with only one age. 

(I'm using num# because the original field is a string. 

What is the problem?

Thank you,

[joaopaulo_delco]

Hi @FernandaNava !

      I think it's treating like measure because you're using the agregated funcion Min().

I Suggest you solve this on script. The app wil increase the performance and your filter will look better.

[FernandaNava]

Thanks for your suggestion. Unfortunately it's not very convenient to solve this on script because the calculation involves several different tables that it wouldn't be convenient to join. 

I tried to solve it following a suggestion from this post:

https://community.qlik.com/t5/Qlik-Sense-App-Development/Convert-Measure-to-Dimension/td-p/1732956

Adding an aggr(). It's now working as a dimension but doing an AND condition even though I wrote an OR in the script. 

=if(Aggr(Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') ), PersonID)> num#(Age1)
or (Aggr(Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') ), PersonID)> num#(Age2))
, '1', '0')

When I click on 1 in a filter pane, it will set filters for PersonID, Age1 and Age2, thus showing only those having a a smaller number for both Age1 and Age2. 

Any idea why this is happening?

[FernandaNava · Accepted solution]

I solved my problem. Posting the solution in case it helps anybody. I just had to put one Aggr for everything, not add it for each comparison:

=if(Aggr(Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age1)
or (Age(min(TestDate), Date(Date#(BirthDate, 'YYYYMMDDhhmmss'), 'DD/MM/YYYY') )> num#(Age2))
, PersonID), '1', '0')