topic Re: Set analysis with Max Function in QlikView

Set analysis with Max Function

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

Hi All,

I have a requirement where in I have to show users with maximum submission count and the table to show only with flag = yes

I had written the following expression : this is working fine for max submission based on users but the flag = yes isn't working(the table is showing flag = no as well):

max(aggr(sum({<Flag = {'Yes'}>}Submission),Users))

Any help will be much appreciated.

Kindly let me know if the requirement is unclear

Thanks,

Anupama Jagan

Re: Set analysis with Max Function

olivierrobin — Thu, 24 May 2018 07:19:54 GMT

hello

did you code your set analysis in the expressions of your table ?

Re: Set analysis with Max Function

PrashantSangle — Thu, 24 May 2018 07:34:10 GMT

can you provide small sample data set on which we can try..

Regards,

Re: Set analysis with Max Function

PrashantSangle — Thu, 24 May 2018 07:36:47 GMT

try below

max({<Flag = {'Yes'}>} aggr(sum({<Flag = {'Yes'}>}Submission),Users))

Regards

Re: Set analysis with Max Function

Anil_Babu_Samineni — Thu, 24 May 2018 10:37:30 GMT

Field name is case sensitive, Might be only flag instead Flag ??

max(aggr(sum({<flag = {'Yes'}>}Submission),Users))

Re: Set analysis with Max Function

ogautier62 — Thu, 24 May 2018 10:45:53 GMT

Hi,

the max is OK, but you must add too a filter to show field :

if( sum({<Flag = {'Yes'}>}Submission) = max( ......), max(.......),)

regards

Re: Set analysis with Max Function

Anonymous — Thu, 24 May 2018 12:16:20 GMT

this works perfect. But when I add region to the table i'm getting multiple values for the users. Because for same two regions that user has max num of submissions. Is there any way to get the first instance of it ?

Re: Set analysis with Max Function

jyothish8807 — Thu, 24 May 2018 12:44:19 GMT

Try this:

max({<Flag = {'Yes'}>} aggr(sum({<Flag = {'Yes'}>}Submission),Users,Region))

Br,

Re: Set analysis with Max Function

Anonymous — Thu, 24 May 2018 13:59:51 GMT

no its still giving duplicates for users

Re: Set analysis with Max Function

jyothish8807 — Fri, 25 May 2018 04:36:07 GMT

Could you please what are your dimensions and expressions ?

Or a layout will be helpful.

Re: Set analysis with Max Function

jyothish8807 — Fri, 25 May 2018 04:38:24 GMT

Try this :

aggr(max({<Flag = {'Yes'}>} aggr(sum({<Flag = {'Yes'}>}Submission),Users,Region)),Users,Region)

else

max(aggr(max({<Flag = {'Yes'}>} aggr(sum({<Flag = {'Yes'}>}Submission),Users,Region)),Users,Region))

Br,

Re: Set analysis with Max Function

PrashantSangle — Fri, 25 May 2018 07:16:43 GMT

nullify Region field in outer set analysis.

try below

max({<Flag = {'Yes'},Region=>} aggr(sum({<Flag = {'Yes'}>}Submission),Users))

Regards,