topic Re: Aggregation and Set anaylsis question in QlikView

Aggregation and Set anaylsis question

Mon, 06 Jul 2015 20:24:50 GMT

The requirement is

Count the students who took the exam before Jan 15, 2015 and populate a grid as shown at the bottom of the image. How can I do this double aggregation like

Aggr(Max(Exam Taken Date),Student Name) and then count for exam dimension. The Student Name dimension will

not show in the final grid. See below the last grid in the image. Please help

Re: Aggregation and Set anaylsis question

sinanozdemir — Mon, 06 Jul 2015 20:32:53 GMT

Hi Faiq,

Please see the screenshot showing the set analysis:

Thanks

Re: Aggregation and Set anaylsis question

Mon, 06 Jul 2015 21:45:11 GMT

Thanks this start in the right direction but I did not explain the example well. In fact each student will have multiple attempts for that exam so I have to check in max (exam taken date) for each student is less than Jan 15, then count those students. See the image below.. I hope this explains it

Re: Aggregation and Set anaylsis question

sinanozdemir — Tue, 07 Jul 2015 00:59:33 GMT

PS, I am editing this and I have just realized what you meant by your example

Hi Faiq,

If I am understanding correctly, you only want to count student names when max(exam taken date) < 1/15/2015. If so, please see the below screenshot:

You also need to aggregate by exam taken date and student name.

Hope this helps

Re: Aggregation and Set anaylsis question

Tue, 07 Jul 2015 15:08:49 GMT

Perfect !!! It works... But I have a question on how this is constructed...

I fully understand how double aggregation and below comparison is working

=Aggr(Max(Date([Exam Taken Date])),[Student Name],Exam)<'1/15/2015'"

but how does the below comparison works when exam taken date = the aggregation code

{<[Exam Taken Date]={"=Aggr(Max(Date([Exam Taken Date])),[Student Name],Exam)<'1/15/2015'"}>}

Thanks for all your help

Re: Aggregation and Set anaylsis question

sinanozdemir — Tue, 07 Jul 2015 16:07:15 GMT

Hi Faiq,

with double quotes in the set analysis, you are basically returning a list of dates that are less than 1/15/2015 so in our example, exam taken date looks at each one of the dates that are returned by the expression.

Set analysis is similar to WHERE clause in SQL which returns an array of items/values/attributes.

Hope it explains.

Re: Aggregation and Set anaylsis question

Tue, 07 Jul 2015 20:35:23 GMT

Thanks that helps a lot.

I have an other question if you do not mind as you are an expert of set analysis.

I am trying to count a column where max_of_activity_date has null values which are highlighted in yellow in my data and it is not working. Here is my code

=Count({$<Max_Of_Activity_Date={"=len(trim(Max_Of_Activity_Date))=0"}>} Project_Lead_GUID)

Re: Aggregation and Set anaylsis question

sinanozdemir — Tue, 07 Jul 2015 20:42:15 GMT

Hi Faiq,

I am no expert yet, but thank you.

Try this:

=Count({1-<Max_Of_Activity_Date={"*"}>} Project_Lead_GUID)

This should give you all the null counts.

Re: Aggregation and Set anaylsis question

Tue, 07 Jul 2015 20:47:09 GMT

It did the count correct but also forced for the other dimension values to show in the grid so now the grid is ignoring other selections on the screen...Hmmm

Re: Aggregation and Set anaylsis question

sinanozdemir — Tue, 07 Jul 2015 21:14:39 GMT

Yeah, that behavior happens because it basically deducts anything with valid value from the whole application which gives you the null values.

Can you send me a screenshot of what you see?

Thanks

Re: Aggregation and Set anaylsis question

sinanozdemir — Tue, 07 Jul 2015 22:18:23 GMT

Or you can try to handle NULL values in the load script which I personally think is less confusing:

I tried to mimic your dataset above:

There are two records with NULL values

And then in the load script, I put the below:

Now, set analysis is cleaner than before and less confusing, I think:

Let me know if this helps.

Re: Aggregation and Set anaylsis question

Anonymous — Wed, 08 Jul 2015 03:56:55 GMT

how about count(if(isnull (Max_Of_Activity_Date),Project_Lead_GUID))

if Project_Lead_GUID has duplicate records, use distinct, so that wud be:

count(distinct(if(isnull (Max_Of_Activity_Date),Project_Lead_GUID)))