Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator II
Creator II

Issue with expression

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)

paulwalker_0-1613453691891.png

 

PFA,

Thanks in advance!

Labels (1)
1 Solution

Accepted Solutions
Vegar
Partner
Partner

Like this?

Vegar_0-1613592679823.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

11 Replies
Vegar
Partner
Partner

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?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulwalker
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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)

Thanks & Regards,
Mayil Vahanan R
paulwalker
Creator II
Creator II
Author

Hi Mayil,

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

paulwalker_0-1613496969546.png

 

 

Vegar
Partner
Partner

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

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulwalker
Creator II
Creator II
Author

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

paulwalker_0-1613569617552.png

output:


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

Vegar
Partner
Partner

Like this?

Vegar_0-1613592679823.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

paulwalker
Creator II
Creator II
Author

Perfect Vegar! - Thank you so much for this 🙂