Aggregation and Set anaylsis question

[#1]

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

[sinanozdemir]

Hi Faiq,

Please see the screenshot showing the set analysis:

Thanks

[#3]

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

[sinanozdemir · Accepted solution]

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

[#5]

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]

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.

[#7]

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)

[sinanozdemir]

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.

[#9]

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]

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