topic Re: Set analysis on all given value in QlikView

Set analysis on all given value

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

Let say I have a table with 3 fields: Subject , Name , Grade

I want to test if in any given subject the average is above 60 – so just want to see Yes or No for that test – do not need a list of all subjects.

How will you recommend doing it elegantly? set analysis?

Re: Set analysis on all given value

sunny_talwar — Thu, 28 Sep 2017 12:26:40 GMT

In a chart? May be just this

Dimension

Subject

Expression

If(Avg(Grade) > 60, 'Yes', 'No')

Re: Set analysis on all given value

surendraj — Thu, 28 Sep 2017 12:27:03 GMT

Here you need to mention on what field you want to apply aggregation.

here you may have marks field.check your data once.

Re: Set analysis on all given value

Anonymous — Thu, 28 Sep 2017 12:32:59 GMT

That will give me the answer for all subjects combine, I want to check each Subject separately, thanks

Re: Set analysis on all given value

sunny_talwar — Thu, 28 Sep 2017 12:34:15 GMT

Why will this give you all subject combined? Subject is a dimension... so the Avg(Grade) will be checked for each subject and will give you yes or no based on the avg for each subject

Re: Set analysis on all given value

Peter_Cammaert — Thu, 28 Sep 2017 12:47:58 GMT

The context of your requirement is important. Where do you want to include this Yes/No indicator? In a straight table? In a straight table with other dimensions besides Subject? On its own in a text box?

Sunny already supplied you with a perfect solution for a Straight Table with a single dimension. What is lacking?

Re: Set analysis on all given value

Anonymous — Thu, 28 Sep 2017 13:12:19 GMT

Thanks Sunny and Peter and Surendra - I just want the answer of yes no in a Text object without giving me the details ... so no Chart... I do not want to see all the results, just need to know if on at least one subject the average on this subject is above 60. Many thanks

Re: Set analysis on all given value

sunny_talwar — Thu, 28 Sep 2017 13:32:54 GMT

So you want to know that if there are 10 subjects, at least one have an average of over 60?

Re: Set analysis on all given value

Anonymous — Thu, 28 Sep 2017 13:33:50 GMT

yes that is what I am after, sorry I was not clear.

Re: Set analysis on all given value

sunny_talwar — Thu, 28 Sep 2017 13:38:25 GMT

Try this

If(Sum(Aggr(If(Avg(Grade) > 60, 1), Subject) >= 1, 'Yes', 'No')

Re: Set analysis on all given value

Anonymous — Thu, 28 Sep 2017 13:45:22 GMT

Error: Error in expression: Sum takes 1 parameter

Re: Set analysis on all given value

sunny_talwar — Thu, 28 Sep 2017 13:46:09 GMT

My bad, try this

If(Sum(Aggr(If(Avg(Grade) > 60, 1), Subject)) >= 1, 'Yes', 'No')

Re: Set analysis on all given value

Anonymous — Thu, 28 Sep 2017 13:49:57 GMT

so beautiful, many thanks