If for each category you "need a different calculation (not just sum(Field1))", then I am again failing to see what is wrong with either an if() or a pick(). You MUST specify every calculation SOMEWHERE. So there's no way around writing 100 calculations. The overhead of a pick() is negligible if you can use rowno().
However, depending on how everything is laid out, you might be able to handle it in the script. I'm guessing you might be able to have a table with ID/expression/result fields:
ID expression result 12345 total sales 5000 12345 avarage monthly sales 300 12345 total cost 4000
And then your chart would have "expression" as a dimension. You'd probably set up the table with a crosstable load:
CROSSTABLE (expression,result) LOAD ID ,expression 1 as "total sales" ,expression 2 as "average monthly sales" ,expression 3 as "total cost" ... ,expression 100 as "whatever" FROM YourTable ;
Now again, you're still having to maintain all 100 expressions, as there's no way around that. But you might get a performance benefit from doing it during the load rather than at execution time. Execution time might be more flexible, however, since it can for instance refer to fields from multiple tables.
I do think a weakness of QlikView is the ability to refer to different meanings of fields, in this case the inability (?) to assemble the field name with string commands and then just use it like a field.
But then I think it might make even more sense to assign certain fields to a variable and to build then your pivot or other charts on basis this variable?
xtrim wrote:it would still force me to use "IF" clause
I guess I'm confused. What's wrong with using an IF clause? Are you worried about performance? In this case, the IF is outside of the summation, so it should be very efficient:
Not that it will perform any differently than an IF, but I'd probably use pick() instead for brevity:
pick(rowno(),sum(Field1),sum(Field2),sum(Field3))
Performance should only be a problem if you embed the pick() in the sum, so I don't recommend this:
sum(pick(rowno(),Field1,Field2,Field3))
But even there, it's possible that QlikView is smart enough to realize that rowno() doesn't have to be calculated every time, and will perform just fine.
