Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

scjoel02
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'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:

1.PNG

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

Capture.PNG

9 Replies
MVP
MVP

Re: Nested Sets In Set Analysis

Try this:

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

scjoel02
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
MVP

Re: Nested Sets In Set Analysis

Or this:

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

scjoel02
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:

3.PNG

Correct Results:

2.PNG

Any ideas?

MVP
MVP

Re: Nested Sets In Set Analysis

Sorry about that. Try this:

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


Capture.PNG

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

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

Capture.PNG