Nested Sets In Set Analysis

[scjoel02]

I have what seems to be a simple problem, where I would like to return a value from a set analysis expression that corresponds to a max date, where the value column is not null.

Consider the data:

DATE	DIMENSION	VALUE	NULLFLAG
1/1/2016	A	.956	0
2/1/2016	A	.967	0
3/1/2016	A	.948	0
1/1/2016	B	.993	0
2/1/2016	B		1
3/1/2016	B		1
1/1/2016	C	.997	0
2/1/2016	C	.959	0
3/1/2016	C	.998	0
1/1/2016	A	.999	0
2/1/2016	A	.989	0
3/1/2016	A	.919	0
1/1/2016	B	.899	0
2/1/2016	B		1
3/1/2016	B		1
1/1/2016	C	.902	0
2/1/2016	C	.949	0
3/1/2016	C	.987	0

I am trying to create an expression that will return the max value for the most recent non-null date.

I've tried just about everything.

I can get the correct date to resolve correctly with this expression: MAX({<DATE, NULLFLAG={0}>}DATE).

I then try to nest that inside my next set to get the max value using: MAX({$<Date={'$(=MAX({<NULLFLAG={0}>}Date))'}>}VALUE).

But the nested set for the date does not get calculated on a row by row basis. It always returns a max date of Mar-16 for each row, ignoring the DIMENSION. So my results look like this:

The value for DIMENSION B should be the max from 1/1/2016, which would be row 4, .993.

I also tried using the AGGR() function, but I got the same results.

Is my syntax incorrect, or do I need to change my approach?

Thanks!

[sunny_talwar]

Try this:

=FirstSortedValue(VALUE, -Aggr(MAX({<DATE, NULLFLAG={0}>}DATE), DIMENSION))

[scjoel02]

Hi Sunny.

FirstSortedValue does not retrieve the max possible value for the max possible non-null date.

Instead, it retrieves the first value in the possible array where the NULLFLAG = 0.

[sunny_talwar]

Or this:

=FirstSortedValue({<DATE, NULLFLAG={0}>} VALUE, -Aggr(MAX({<DATE, NULLFLAG={0}>}DATE), DIMENSION))

[scjoel02]

As seen below, the FirstSortedValue expression only works for the NULL VALUE DIMENSION.

I need it to work across all dimensions and selections.

Using FirstSortedValue causes errors with dimensions A and C.

FirtSortedValue Results:

Correct Results:

Any ideas?

[sunny_talwar]

Sorry about that. Try this:

=FirstSortedValue(DISTINCT {<DATE, NULLFLAG={0}>} VALUE, -(DATE+VALUE))

[scjoel02]

Getting closer here.

This is good for retrieving the correct value, but it doesn't seem to respect date selections.

[sunny_talwar]

That is because we ignore DATE in the expression. You don't want to ignore date selection? Try this:

=FirstSortedValue(DISTINCT {<NULLFLAG={0}>} VALUE, -(DATE+VALUE))

[scjoel02]

Removing the date exclusion in the set causes the null value row to break, and we're back to square one.

The desired behavior would be to give me the max value from the max available date, respecting selections, unless that value is null. If it is null then it should be the value from the max available date where the value is not null.

I guess I can do this by adding an IF ISNULL() in front of the FirstSortedValue expressions, but ultimately I would be looking for a more elegant solution than an IF statement.

I'll go with this for now.

Thanks so much for your help and quick replies!!!

[sunny_talwar · Accepted solution]

May be this:

=Alt(FirstSortedValue(DISTINCT {<NULLFLAG={0}>} VALUE, -(DATE+VALUE)), FirstSortedValue(DISTINCT {<DATE, NULLFLAG={0}>} VALUE, -(DATE+VALUE)))