topic Re: Urgent Help Needed in QlikView

Urgent Help Needed

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Urgent Help Needed

swuehl — Thu, 19 May 2016 14:24:08 GMT

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.

Re: Urgent Help Needed

Fri, 20 May 2016 07:00:43 GMT

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

Re: Urgent Help Needed

swuehl — Fri, 20 May 2016 09:14:19 GMT

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

)

Re: Urgent Help Needed

sunny_talwar — Sat, 21 May 2016 18:27:08 GMT

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

Re: Urgent Help Needed

swuehl — Sat, 21 May 2016 18:45:25 GMT

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

Re: Urgent Help Needed

sunny_talwar — Sat, 21 May 2016 19:11:33 GMT

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

Re: Urgent Help Needed

swuehl — Sat, 21 May 2016 19:18:28 GMT

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

Re: Urgent Help Needed

Sun, 22 May 2016 13:04:34 GMT

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?!

Re: Urgent Help Needed

swuehl — Sun, 22 May 2016 16:14:28 GMT

Re: Urgent Help Needed

swuehl — Sun, 22 May 2016 22:05:44 GMT

maneck.mehta wrote:

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?!

You can edit an existing thread (just use Actions - Edit) or create a new reply and open the advanced editor if you want to attach files.

Uploading a Sample

May I request these things from you for future requests?

Please don't post the same request multiple times (see above for reasoning)
Please use a descriptive title (NOT 'Urgent Help needed'):
- Nothing is urgent here in the community, most of the people are helping in there free time and don't like to be pushed
- Using a descriptive title makes it easier for people to find the relevant posts
If possible, attach input records and your requested results to your original post, it saves a lot of time not needing to ask for this.
If something is not working for your like in "I tried the code but it didn’t work for me", please don't forget to describe what you tried in detail, what you expected to get and what you got instead. Just stating 'Doesn't work' is not helping in improving the solution at all.
If you don't like the answers given and you've found the correct answer elsewhere, just post a note that your request is resolved (a link to the solution would be nice), then set the status of your thread to ASSUMED ANSWERED.

Flagging a post as CORRECT answer that just asks you to close this thread is misleading and not helping anybody looking for a solution similar to your original request.

Stefan

Re: Urgent Help Needed

sunny_talwar — Mon, 23 May 2016 00:42:51 GMT

Great explanatory response Stefan. I think all these points, if followed, can make this community a much better place. I am glad you took the time to put these points as I am going to use this thread as a recommendation for other posters as well.

Best,

Sunny

Re: Urgent Help Needed

Mon, 23 May 2016 05:44:11 GMT

@SWuehl Please see my explanation below on all your points,

I gave you a full explanation on why i had to repost this! will copy paste it here once again

"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?!"

I used a title what i felt was appropriate at that time. If there were issues with the title the post wouldn't have passed moderation (as is the case with all posts in this community!)
Yes,People are helping out in their free time and if you see something you don't like or if you feel you are being pushed then you can choose to leave/not answer rather than being so sore about it! (I don't see anyone else complaining apart from you just because i reposted this).
Please don't make your own set of guidelines as every user is free to post what he wants and if it is something not acceptable there is moderation in place as well. All posts are monitored/moderated before being posted.
The reason i marked sunny's answer as the correct one is because it is the correct one!! Also it is pointing the user to the correct thread with the full solution . I don't see him having fits of rage for not getting credit for some post!

I won't be replying further. Since you do this in your free time as well i would suggest there are better things to do then be so sore about an online post which by the way was not reposed with the whole purpose of offending you!

Re: Urgent Help Needed

swuehl — Mon, 23 May 2016 09:25:28 GMT

Just to clarify my previous statement:

It's not about getting points or not, it's basically about creating a more efficient forum in the long run.

Maybe I haven't found the correct wordings in above post, all I asked about was to kindly consider above when posting your next request.

Hope it's clarified.

Re: Urgent Help Needed

swuehl — Mon, 23 May 2016 09:30:01 GMT

P.S. I know you won't answer, but could you explain what the essential difference is between my first suggested expression for the average calculation

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

Max 3 value average:

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

and Sunny's solution:

Avg(TOTAL <Market> Aggr(If(Rank(Sum([Unique Leads 1 Month])/Sum([Active Contacts])) <= 3,

Sum([Unique Leads 1 Month])/Sum([Active Contacts])), Market, Month)

(just to understand and learn ...)

Re: Urgent Help Needed

Mon, 23 May 2016 09:35:13 GMT

Yes, I too agree with the points you mentioned but considering the tonality i replied accordingly. This was my first post here so u'l need to excuse me if it wasn't really presentable. In the following posts i tried to provide as much data as i could in a presentable manner.

Thanks for all your help & inputs here!

Re: Urgent Help Needed

swuehl — Mon, 23 May 2016 11:32:55 GMT

English is not my mother language, which part of my answers have you read as offensive or have you disliked in its tonality?