Why my expression is not working?

[brunolelli87]

Hello guys!

On a bar chart, I have the field STCK as Dimension, and the following expression:

=FirstSortedValue(VDAF, -DVPG)/(FirstSortedValue(LOPP, -PERI)/FirstSortedValue(NDAN, -PERI))

This expression is working perfectly!

Now, I'm trying to create a conditional expression that will show another bar when the user selects just one company (STCK). This new expression will calculate the average of the same expression based on the company's segment (SETO).
So, I created the following expression:

=avg(TOTAL{<STCK,SETO=p(SETO)>}(FirstSortedValue(VDAF, -DVPG)/FirstSortedValue(LOPP, -PERI)/FirstSortedValue(NDAN, -PERI)))

 
And the condition is:

GetSelectedCount(STCK) = 1

The expression is OK - with no errors - but shows nothing on a bar chart.
When I change the equation part of my expressions something like this:

=avg(TOTAL {<STCK,SETO=p(SETO)>}2/2)

Then, it shows... so I think there is a problem with my equation!

How can I solve it?
How can I show the average of the first equation based on the selected company's segment?

Thanks

[agigliotti]

maybe this:
=Avg( TOTAL Aggr( ( FirstSortedValue(VDAF, -DVPG)
/
( FirstSortedValue(LOPP, -PERI)
/
FirstSortedValue(NDAN, -PERI) ) )
, STCK, SETO ) )

I hope it can helps.

[brunolelli87]

Hello!

Thanks for your help... your expression works, but the result is not what I'm expecting.

Your expression is returning the same value always.

[agigliotti]

let's try to remove TOTAL keyword.

[brunolelli87]

Hello!

Thanks for your help, but it's still not working!

Any other idea?

[brunolelli87]

Hello guys

Any other idea?

[brunolelli87 · Accepted solution]

Hey, I got it!

This is what I did!

=AVG( TOTAL {<STCK,SETO=p(SETO)>}

AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}VDAF, -DVPG), STCK, SETO) /

	(
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}LOPP, -PERI), STCK, SETO) /
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}NDAN, -PERI), STCK, SETO)
	)

)

Thanks for this community!