expression name showing as a bad field name

[ellyodelly]

I created an expression that calculates the days between two dates interval(date1 - date2,'d')  works ok   call it Cutoffdaysdif.

for testing i am just trying to show the max of that expression.  I have found examples of other QlikView users doing this but I keep getting a bad field name.

I can assing the new expression = to the existing but as soon as I try an aggregation on it-  it fails as a bad name.

ultimately I would like to use the Cutoffdaysdif in a set analysis- that should work correct?

what am i missing?  I do have a snap shot attached.

thanks, Elly

[Gysbert_Wassenaar · Accepted solution]

Well, that's just the way it is. You can't use column labels as field names in aggregation functions. You'll have to use the expression itself and then you'll need to use the aggr function on it first. Otherwise there's not a list of values to find the max value of. max(sum(Sales) won't work, max(aggr(sum(Sales),Product)) would (if you have a dimension Product and a field Sales).

[ellyodelly]

interesting-  or i could put the same logic with in my set analysis and remove the cutoffdaysdif?

however i have a set modifier error I can not figure out

sum({$< interval(varAgeCutoff-[AR Transaction Date],'d') = {"> 0"}, interval(varAgeCutoff - [AR Transaction Date],'d') = {"< 31"} >} [AR Invoice Amt])

[Gysbert_Wassenaar]

You can only use fields in set analysis expressions on the left hand of a comparison condition. So you'd have to rewrite it to this (if I understand it correctly):

sum({$< [AR Transaction Date] = {">$(varAgeCutoff)-31<$(varAgeCutoff)"} >} [AR Invoice Amt])

Also a set analysis expression is calculated once per chart, not per row. The set is calculated first and then the dimensions and expressions are applied to the records in the set. That means if you use AR Transaction Date as a dimension in your chart you can't use it in the set analysis expression and expect it to do what you want and probably expect it to do.

[ellyodelly]

I did not know that~  what about variables?  when are they calculated? by document or by row?

I do appreciate your help-  as you can tell I am a definate rookie

[Gysbert_Wassenaar]

If you define a variable (e.g. vMyVar) as =sum(Sales) then it is calculated anything something in the document changes, like for example selections or a chart is added or changed. It's otherwise static, meaning not dependant on values of dimensions if used in a chart. If you define the variable as sum(Sales) then it's evaluated when you dollar-expand it: $(vMyVar). That means if you use it in an expression it will take the charts dimension values into account, i.e. it's calculated per row.

[ellyodelly]

this information is most helpful.  Thank you for your time..