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

34 Replies
Not applicable
Author

Ok, I will. I am currently replacing the variable with the expression to see if that gets me further.

Not applicable
Author

Stefan,

The long hand version gives an expected answer:

= If

(

RowNo()= ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

, $(vOpenErrorsTbaTbc)

,If( RowNo()> ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

, ABOVE( $(vOpenErrorsTbaTbc), RowNo()- ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) ) )

))

Where the one using variables gives null:

(

If( RowNo()= $(vStartBurndownRowV), $(vOpenErrorsTbaTbc),

If(RowNo()>$(vStartBurndownRow), ABOVE( $(vOpenErrorsTbaTbc), RowNo()-$(vStartBurndownRow))

))

)

Hmmmm.

Don

swuehl
MVP
MVP

You could check how the dollar sign expansion is done clearing the label of the expression and then hover with your mouse over the label in your table (this should give the expression after dollar sign expansion).

Regards,

Stefan

Not applicable
Author

Stefan,

Not sure what you mean, will try a few other things.

Note, this is the fully exploded expression that does the trick however:

=

(

If

(

RowNo()= ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

, $(vOpenErrorsTbaTbc)

,If ( RowNo()> ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

, ABOVE( $(vOpenErrorsTbaTbc), RowNo()- ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) ) )

)

)

)

-

(

rangesum

(

ABOVE(

If(RowNo()= ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

,$(vOpenErrorsTbaTbc)/$(vNumberBurnDownWeeks)

, If(RowNo()> ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

, ABOVE( $(vOpenErrorsTbaTbc)/$(vNumberBurnDownWeeks), RowNo()- ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) ))

)

)

, 0

, RowNo() - ( Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0) ,YrWk) ) )

)

)

)

swuehl
MVP
MVP

Not sure if this is related to your problem in any way, but you may wanna have a look into

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

which is describing the different evaluation of variables (similar to using Set / Let statements in the script).

What I meant with checking the dollar sign expansion is related to this. Sometimes it's really enlightening if you see the expression after the dollar sign expansion or variable replacement took place.

One way to see this if to look at the header in your table, if no label in expression tab is set. The header will then just show the expression, but after the dollar sign expansion took place. So you can actually see, what is replacing the variable. Maybe this gives you any insight to your problem, not sure though.