Urgent Help Needed

[#1]

I have this unsolved issue in hand which i need to deliver quickly and i am still stuck with no answers so any help would be appreciated for this problem.

As you see in the pic i have some data for 7 countries & 7 months.

What i Need :

For every Country there are 6 values in the column (Ignore month column).

1)Sort the 6 values in Ascending to Descending order.

2)Not after sorting get the Avg(Minimum 2 values) & Avg(Maximum 3 values) . These values will differ for every country as the 6 values are different.

[swuehl]

It would be easier to help you if you would post a table file or INLINE table as text with some sample records to play with, instead of just a screenshot.

You can try something like this (assuming your expression is Sum(Value) [which is certainly not, just replace with your expression]):

Min 2 value average:

=Avg(TOTAL<Country> Aggr(If(Rank( -Sum(Value),4)<=2, Sum(Value) ), Country, Month))

Max 3 value average:

=Avg(TOTAL<Country> Aggr(If(Rank( Sum(Value),4)<=3, Sum(Value) ), Country, Month))

You probably need to adapt the expression to the context you want to use them in.

[#3]

I tried the code but it didn’t work for me. Attaching the sample test file which can be uploaded & used in QV

[swuehl]

It does work, you just need to adapt the TOTAL qualifier if you don't really have Numbers for all Month and Markets.

Straight table chart with two dimensions Month and Market:

Three expressions:

=Sum(Numbers)

=Avg(TOTAL Aggr(If(Rank(TOTAL -Sum(Numbers),4)<=2, Sum(Numbers)), Market, Month))

=Avg(TOTAL Aggr(If(Rank(TOTAL Sum(Numbers),4)<=3, Sum(Numbers)), Market, Month))

One background color expression for first expression:

=If(Sum(Numbers) < column(2), LightGreen(),

     If(Sum(Numbers) > column(3), lightred(), Yellow())

)

[sunny_talwar · Accepted solution]

I think the issue has been resolved (Conditional based display of Traffic Lights in data), I would suggest closing this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

[swuehl]

Sunny, this thread is much older...

Maneck.Mechta, please refrain from posting the  same request multiple times, it just makes it hard to follow a discussion (and it's kind of frustrating that you are posting more detailed information to another, new thread instead of trying to cooperate with the people who are trying to help you).

[sunny_talwar]

Stefan -

the reason I responded to this thread is for you to be rewarded for your effort. Although I don't approve multiple threads, but if it has been created for any reason, the effort of people who tried to help should be rewarded in one way or the other.

P.S. not sure how this is old, this is just 2 days from today

[swuehl]

Much older in Sunny-Answering-Speed-Time ...

[#9]

swuehl i provided mre info to the same problem posted again ( in detail this time!) bcos i dont think there is a way to edit the current one and i wanted to make sure everything is captured which wasn't the case earlier! i want any new ppl who see the issue to hav all the info in one shot rather than having to go thru the thread in bits n pieces ! so sorry i didnt think of you before i posted this?!

[swuehl]