topic Invalid dimension using expression in App Development

Invalid dimension using expression

43918084 — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: Invalid dimension using expression

tresB — Mon, 21 Dec 2020 06:25:23 GMT

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

Re: Invalid dimension using expression

43918084 — Mon, 21 Dec 2020 06:54:23 GMT

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

Re: Invalid dimension using expression

tresB — Mon, 21 Dec 2020 07:08:19 GMT

@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')))

Re: Invalid dimension using expression

MayilVahanan — Mon, 21 Dec 2020 07:32:48 GMT

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

Re: Invalid dimension using expression

sunsun566 — Mon, 21 Dec 2020 07:46:26 GMT

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'
)))))))))))

Re: Invalid dimension using expression

43918084 — Mon, 21 Dec 2020 07:47:42 GMT

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?

Re: Invalid dimension using expression

MayilVahanan — Mon, 21 Dec 2020 07:50:09 GMT

HI @43918084

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

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

Re: Invalid dimension using expression

43918084 — Mon, 21 Dec 2020 07:53:23 GMT

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 🙂

Re: Invalid dimension using expression

jamielim — Wed, 20 Dec 2023 05:35:02 GMT

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-involves/m-p/2154867#M243043