Invalid dimension using expression

[43918084]

I would like to create a Dimension field using expression but I get invaldi dimension

My expression is

=if([TRADE DATE (P0561)]>min([ACCTG DATE (P0045)]), 'new',
if([MATURITY DATE (P0288)]<max([ACCTG DATE (P0045)]),'matured',
if([MATURITY DATE (P0288)]>max([ACCTG DATE (P0045)]) and [Current$]=0 and [Last$]>0,'terminated',
'existed')))

Below is my expected result.  Hope I can get some guidance.  Many thanks.

status	GLOBAL TRADE NUM (P0221)	TRADE DATE (P0561)	MATURITY DATE (P0288)	ACCTG DATE (P0045)	Current$	Last$
Exist	1000328H	20120419	20200423	20201130	20	30
New	1000328H	20120119	20200423	20201130	10	0
Matured	1001853H	20120423	20190425	20191231		50
Terminated	1001853H	20120423	20200425	20191231		20

[tresesco]

@43918084 , Try putting 'Total' identifier in all min/max functions in your calculated dimension.

[43918084]

Thank you very much Tresesco.  I apologise that I don't quite get what you mean.  Would you mind to give me an example?

[tresesco]

@43918084 , Try this:

=if([TRADE DATE (P0561)]>min(Total [ACCTG DATE (P0045)]), 'new',
if([MATURITY DATE (P0288)]<max( Total [ACCTG DATE (P0045)]),'matured',
if([MATURITY DATE (P0288)]>max( Total [ACCTG DATE (P0045)]) and [Current$]=0 and [Last$]>0,'terminated',
'existed')))

[MayilVahanan]

Hi @43918084 

In dimension, you can't use aggregation function like Min(), sum(), it will display invalid dimension. 

To avoid this, you need to aggr() function, like

Aggr(if(urcondition), yourdimensions)

Hope it helps

[sunsun566]

Hi

Hope it helps you 🙂 from my Stock Aging

IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 721 , '24M Up' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 541 , '18-24M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 451 , '15-18M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 361 , '12-15M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 181 , '06-12M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 151 , '05-06M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 121 , '04-05M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 91 , '03-04M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 61 , '02-03M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 31 , '01-02M' ,
IF(AGGR(Max([Transaction date]) - Max([Manufacturing date]),[Lot number]) >= 0 , '< 01M' , 'Other'
)))))))))))

[43918084]

Thank you very much Tresesco and MayilVahanan.  

I have tried both and the Agg() advice from MayilVahanan returns no error.

=Aggr(if([TRADE DATE (P0561)]>min([ACCTG DATE (P0045)]), 'new', if([MATURITY DATE (P0288)]<max([ACCTG DATE (P0045)]),'matured', if([MATURITY DATE (P0288)]>max([ACCTG DATE (P0045)]),'terminated', 'existed'))),'status')

But it does not give me any Value, it only returns '-'.  Just wondering if I have done something wrong?

[MayilVahanan]

HI @43918084 

Here 'status' is not the dimension, its string. May be try like below

=Aggr(if([TRADE DATE (P0561)]>min([ACCTG DATE (P0045)]), 'new', if([MATURITY DATE (P0288)]<max([ACCTG DATE (P0045)]),'matured', if([MATURITY DATE (P0288)]>max([ACCTG DATE (P0045)]),'terminated', 'existed'))),[GLOBAL TRADE NUM (P0221)])

As suggested by @tresesco , you can include Total also.  its upto ur requirement.

[43918084 · Accepted solution]

Thank you very much Sunsun566 and Maylivahanan

I have tried the advise from Maylivahanan and it works.  Thank you very much all for your expertise.

Merry Xmas ahead 🙂

[jamielim]

Hi,

I have a similar issue raised, but not too sure how & if I need to use the Aggr function to resolve it. My question is on the following link. Can anyone in this discussion advise? Thank you.

https://community.qlik.com/t5/New-to-Qlik-Sense/Set-dimension-to-null-based-on-calculated-value-that...