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: 
Not applicable

How to have the Max RowNo from an expression available for all rows?

The below provides the RowNo for the latest retrieved value in the system.

// expression 1:

= if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)

My impression is that it would be simple to have that RowNo available on all rows by wrapping the above expression in MAX, it does not work. 

// expression 2:

= MAX(   if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)   )

Does anyone see what I am doing wrong in this very simple expression?

Simply need to be able to have that max RowNo from the first expression available on every for another expression.

Thanks for any assistance....

D

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe, I also tried with clearing the selection one can make in the selection multi box.

If you use an expression with set identifier 1 as you did in your posted expression, I believe you will always get the '46' max rownumber regardless of selections.

But if  you select Week(s) from your multibox, your future row v maybe e.g. only 30, so the expression doesn't seem right to me.

Honestly, I haven't fully understood what you are doing and your table looks too complex to get a quick insight.

Maybe you could work with a set identifier {1} or by clearing the four field you can select in your multibox, like

{<component=, subcomponent=,cf_rpn=,retrieved=>}

- maybe not.

--

Stefan

View solution in original post

34 Replies
swuehl
MVP
MVP

You can't just embed one aggregation funcion into another (max() into max() ) like this.

You would need to use advanced aggregation, using a dimension list for the aggr() function.

Honestly, I haven't fully understood your issue, could you post a sample?

Not applicable
Author

Swuehl, can you please repost your message.  I removed one of my messages and it took yours as well. thanks....

swuehl
MVP
MVP

Will try...

The expression could look like

=max(total aggr(NODISTINCT if(max(TOTAL retrieved) = max(retrieved), RowNo(),0), YrWk))

Not applicable
Author

How about this, adding in use of Set Analysis which basically does the calc regardless of the selection filter but honors the dimensions: 

=Max({1} total aggr(NODISTINCT if(max({1}TOTAL retrieved) = max({1} retrieved), RowNo(),0) ,YrWk))

swuehl
MVP
MVP

Maybe, I also tried with clearing the selection one can make in the selection multi box.

If you use an expression with set identifier 1 as you did in your posted expression, I believe you will always get the '46' max rownumber regardless of selections.

But if  you select Week(s) from your multibox, your future row v maybe e.g. only 30, so the expression doesn't seem right to me.

Honestly, I haven't fully understood what you are doing and your table looks too complex to get a quick insight.

Maybe you could work with a set identifier {1} or by clearing the four field you can select in your multibox, like

{<component=, subcomponent=,cf_rpn=,retrieved=>}

- maybe not.

--

Stefan

Not applicable
Author

Stefan,

I must be missing something, doing a very similar expression as before  but getting issue, you see what is incorrect?

This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

=if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

This expression that makes that number available on all rows gives an incorrect answer of 36, you see what I am diong wrong:

=Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )              

swuehl
MVP
MVP

Getting closer.

I believe aggr() function will sort the given dimensions by load order, and you can't change that behaviour.

Your load order of field YrWk seems not to be numerical asc or text asc. So your second expression will have a different row number representing value '2012-5' then your table (which is sorted properly). If you sort your table dimension by load order, you will see the expression will match, but the overall outcome is not what you want.

Try using an appropriate load order?

Regards,

Stefan

Not applicable
Author

Stefan,

Thanks... I added an ID column and sorted the table in excel  by the ID column, but no help.  Must be I have to sort the excel file on the way into Qlikview, is that what you are saying?  Is that as easy as adding an ASC to the load by chance?

Thanks,

Don

swuehl
MVP
MVP

Not sure what you want to do with excel, is Excel your data source? I currently don't have access to the last version of your qvw-file, so I can't look at the load statement.

The load order of field YrWk is determined by the order your distinct values are read into your data model, this could be done using multiple loads, concatenation etc.

You could try creating an autogenerated table with field YrWk created for the range you need upfront, sorted ascending, then load your other tables in, then I think you can drop your initially created table again.

Or alternatively take care that the field values are read in sorted from your table source(s).