topic Re: Nested Sets In Set Analysis in App Development

Nested Sets In Set Analysis

scjoel02 — Tue, 28 Jun 2016 14:55:57 GMT

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

.956

2/1/2016

.967

3/1/2016

.948

1/1/2016

.993

2/1/2016

3/1/2016

1/1/2016

.997

2/1/2016

.959

3/1/2016

.998

1/1/2016

.999

2/1/2016

.989

3/1/2016

.919

1/1/2016

.899

2/1/2016

3/1/2016

1/1/2016

.902

2/1/2016

.949

3/1/2016

.987

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!

Re: Nested Sets In Set Analysis

sunny_talwar — Tue, 28 Jun 2016 15:41:06 GMT

Try this:

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

Re: Nested Sets In Set Analysis

scjoel02 — Tue, 28 Jun 2016 15:46:50 GMT

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.

Re: Nested Sets In Set Analysis

sunny_talwar — Tue, 28 Jun 2016 15:49:27 GMT

Or this:

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

Re: Nested Sets In Set Analysis

scjoel02 — Tue, 28 Jun 2016 16:08:21 GMT

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?

Re: Nested Sets In Set Analysis

sunny_talwar — Tue, 28 Jun 2016 16:29:35 GMT

Sorry about that. Try this:

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

Re: Nested Sets In Set Analysis

scjoel02 — Tue, 28 Jun 2016 16:40:51 GMT

Getting closer here.

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

Re: Nested Sets In Set Analysis

sunny_talwar — Tue, 28 Jun 2016 16:49:54 GMT

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))

Re: Nested Sets In Set Analysis

scjoel02 — Tue, 28 Jun 2016 17:00:43 GMT

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!!!

Re: Nested Sets In Set Analysis

sunny_talwar — Tue, 28 Jun 2016 17:12:28 GMT

May be this:

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