topic Re: Why my expression is not working? in QlikView

Why my expression is not working?

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Why my expression is not working?

agigliotti — Fri, 22 May 2020 13:04:02 GMT

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

I hope it can helps.

Re: Why my expression is not working?

brunolelli87 — Fri, 22 May 2020 13:11:25 GMT

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.

Re: Why my expression is not working?

agigliotti — Fri, 22 May 2020 13:43:53 GMT

let's try to remove TOTAL keyword.

Re: Why my expression is not working?

brunolelli87 — Fri, 22 May 2020 15:00:10 GMT

Hello!

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

Any other idea?

Re: Why my expression is not working?

brunolelli87 — Sat, 23 May 2020 17:04:45 GMT

Hello guys

Any other idea?

Re: Why my expression is not working?

brunolelli87 — Sat, 23 May 2020 20:08:01 GMT

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!