Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregation and Set anaylsis question

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

capture.jpg


1 Solution

Accepted Solutions
sinanozdemir
Specialist III
Specialist III

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:

Capture.PNG

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

Hope this helps

View solution in original post

11 Replies
sinanozdemir
Specialist III
Specialist III

Hi Faiq,

Please see the screenshot showing the set analysis:

Capture.PNG

Thanks

Not applicable
Author

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

capture.jpg

sinanozdemir
Specialist III
Specialist III

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:

Capture.PNG

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

Hope this helps

View solution in original post

Not applicable
Author

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

sinanozdemir
Specialist III
Specialist III

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.

Not applicable
Author

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)

capture.jpg

sinanozdemir
Specialist III
Specialist III

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.

Not applicable
Author

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

sinanozdemir
Specialist III
Specialist III

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