Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
scjoel02
Partner - Contributor
Partner - 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!

1 Solution

Accepted Solutions
sunny_talwar

May be this:

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

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

Try this:

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

scjoel02
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor
Partner - Contributor
Author

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?

sunny_talwar

Sorry about that. Try this:

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


Capture.PNG

scjoel02
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor
Partner - Contributor
Author

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

May be this:

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

Capture.PNG