Issue with expression

[paulwalker]

Hi Community,

I'm comparing two fields using with states, but not giving any value in textbox - same expression if I use in straight table working fine with dimension InsCode.

Can you please correct me for show in textbox ?

=IF(Aggr(Only({2} InsCode), InsCode)=Aggr(Only({1} InsCode), InsCode), AVG({1} AVGSAL))

in text value should be 2804.3333, for those selections  (I'm comparing with InsCode for two states)

PFA,

Thanks in advance!

[Vegar]

When you use it in a straight table with CODE as a dimension your aggr(only()) expressions returns a single value, but when used in a text box with more than one CODE in your selection your expression will return an "array" of values and the expression will return null. The same thing will happen to your total row in the straight table.

What are you expecting as an an output from your expression in the textbox?

[paulwalker]

Thanks for quick response Vegar.

I have selected two states and expecting matching InsCode value , that would be 2804.3333.

Have attached sample file.

[paulwalker]

Experts, Anyone have experienced compare two states?

[MayilVahanan]

Hi @paulwalker 

Try something like below

=If(WildMatch(SubField( Concat(Distinct {1} InsCode,'|'),'|'), only({2} InsCode)), AVG({1} AVGSAL),0)

[paulwalker]

Hi Mayil,

this expression giving some result - it's taking overall average, but it should be matching value 2804.3333.

[Vegar]

@MayilVahanan  Be careful creating an alternate state named '1', it can be confused with the full set {1}. In my attached response I have replaced the states '1' and '2' with 'StateA' and 'StateB'.

The expression I have used in the two new objects is:
=AVG({StateA<InsCode=P(StateB::InsCode)>} AVGSAL)

[paulwalker]

Hi Vegar,

Almost there, but still facing one issue..

Below chart should be two records one for 3000 & one for 38000 and AVGSAL would be the same for two records 2804.3333

Tried with this, but giving null 

=AVG(AGGR(AVG({StateA<InsCode=P(StateB::InsCode)>} AVGSAL), InsCode, Salary))

output:

InsCode                 Salary     AvgSal
AK.02.001.P40     38000     2804.333333
AK.02.001.P41     3000        2804.333333

[Vegar · Accepted solution]

Like this?

The issue with your old app was that the aggr will per default only set the value to one row of your aggr combo. The other row was hidden because the chart excluded zero values.  You can adjust this Aggr() behaviour by using NODISTINCT like this:

=AVG(AGGR(NODISTINCT AVG({StateA<InsCode=P(StateB::InsCode)>} AVGSAL), InsCode))

[paulwalker]

Perfect Vegar! - Thank you so much for this 🙂