# Qlik Sense App Development

New Contributor

## Nested Sets In Set Analysis

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
DIMENSIONVALUENULLFLAG

1/1/2016

A

.956

0

2/1/2016A.9670
3/1/2016A.9480
1/1/2016B.9930
2/1/2016B1
3/1/2016B1
1/1/2016C.9970
2/1/2016C.9590
3/1/2016C.9980
1/1/2016A.9990
2/1/2016A.9890
3/1/2016A.9190
1/1/2016B.8990
2/1/2016B1
3/1/2016B1
1/1/2016C.9020
2/1/2016C.9490
3/1/2016C.9870

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

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!

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Nested Sets In Set Analysis

May be this:

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

9 Replies
MVP

## Re: Nested Sets In Set Analysis

Try this:

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

New Contributor

## Re: Nested Sets In Set Analysis

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.

MVP

## Re: Nested Sets In Set Analysis

Or this:

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

New Contributor

## Re: Nested Sets In Set Analysis

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?

MVP

## Re: Nested Sets In Set Analysis

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

New Contributor

## Re: Nested Sets In Set Analysis

Getting closer here.

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

MVP

## Re: Nested Sets In Set Analysis

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

New Contributor

## Re: Nested Sets In Set Analysis

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

MVP

## Re: Nested Sets In Set Analysis

May be this:

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