Help needed with Nested SetAnalysis

[#1]

Hi there!

I need some help with a nested SetAnalysis that I work on. For this discussion I made a simplified example of my problem.

My source data

YearMonth	myDate	Sales	numDate
201101	1-1-2011	1	40544
201101	2-1-2011	2	40545
201102	1-2-2011	3	40575
201102	2-2-2011	4	40576

What I want to achieve is to show, per YearMonth the sales where myDate is greater than the minimum of myDate in that YearMonth.

So the result should be this:

201101 >>> 2

201102 >>> 4

Attached I have a QVW of this example but after a lot of trying I don't get it working. Which set expression do I need?

I thought I needed this:

=sum( {$<myDate={'>$(=min(myDate))'}>} Sales)

but that only produces the expected value for 201101 and not for 201102

Thanks in advance for the help!

[swuehl · Accepted solution]

Set analysis will be evaluated once per chart and will therefore not take the current dimension values into account (like looking at the current YearMonth and calculating the min(myDate) per YearMonth. So, if you want to use set analysis, you probably need to add a search expression with an aggregation function to select the correct myDate values per YearMonth:

=sum(total<YearMonth> {$<myDate={"=myDate>aggr(min(total<YearMonth> myDate),YearMonth,myDate)"}>} Sales)

[#3]

Thanks! That indeed gives the correct outcomes!

I'm not 100% sure if this solves my bigger problem as well, but I hope so...

I'll dive more into usage of total< >

Based on your expression, I found info in the help with this topic: Nested aggregations with total qualifier. I'll study on this further this week!

[#4]

I now get the idea... One related question after my investigation of this...

Your expression (again stated below) works perfectly, and after investigation (with your explanation in mind) I know why...

=sum(total<YearMonth> {$<myDate={"=myDate>aggr(min(total<YearMonth> myDate),YearMonth,myDate)"}>} Sales)

I was a bit surprised about your expression, and to better understand things, I'd like to know why the 2 slightly different expressions don't work...

[1] expressions without dollarsign expansion?

- I thought that when functions need to be used in a set (like {"min(myDate)"}, this can only be done using a dollar sign expansion (like  {"$(=min(myDate))"}  )

- according to your expression, this is not true: a part of your set expression is "=mydDate>aggr(.........)" (not using $(=.......)

- To be more precise, the expression below doesn't even work correctly (returns 0 and 0)... Could you explain why?

//without dollar sign expansion
=sum(total<YearMonth> {$<myDate={"=myDate>$(=aggr(min(total<YearMonth> myDate),YearMonth,myDate))"}>} Sales)

[2] expressions without dollarsign expansion?

- I was a bit surprised about the {$<myDate={"=myDate>aggr( part and thought =myDate could be omitted

- But that is not the case, since the expression below doesn't work (returns 0 and 0)

//[1]without =myDate
//=sum(total<YearMonth> {$<myDate={">aggr(min(total<YearMonth> myDate),YearMonth,myDate)"}>} Sales)

[3] how to check your setexpression easily

- When building more complex expressions, I often don't know why the value is incorrect (0 or null).

- How can I best check my set expression?

- Can I for example print the filter as string as an additional column?

A lot of questions, I hope someone can shed some additional light and/or refer me to handy references!

Know that I appreciate your help a lot!