Does anyone on the forum know if expressions in pivot table with two (one horizontal and one vertically placed) dimensions can be counted? I attach here an example table. What I want is to have a field next to CustomerID that counts the expressions for each CustomerID - i.e. in the column RowNo() to have 1,2,3 for CustomerID=1, then again 1,2,3 for next CustomerID and so on. RowNo() obviously doesn't work. Any help is greatly appreciated.
Maybe the solution is possible if there is a way to refer to the name of each expression, because I have a separate table with the expression names and their consecutive numbers.
Does qlikview remember somehow expression name (as it is written in letters) in a way that we can refer to it? If I remember right, it is possible to refer to expression names (in " ") in formulas of other, consecutive expressions . Do you know how can we call ExpressionName in column(1), or column(3) for example?
I want to have a counter for my expressions, so 1 against Qty, 2 against Sales, 3 against S/Q and then for the next Customer ID again 1,2,3 and next again 1,2,3 and so on. I want to create a key like CustomerID*100+ExpressionNumber.
I will need to write all my expression formulas (I have more than 40 expressions with quite complicated formulas in some of them) in a separate, excel file, but it will work in the way you suggested. I was curious to know if there is some easy way to do it, like referencing to the Expression Names themselves inside the chart. It is possible to reference expression name when writing another expression (from within the espressions) but when I try to reference it from the (calculated) dimensions probably it doesn't work and anyway I don't know if there is function in Qlikview that will return that expression names's columnNo , something like ColumnNo("GROSS PREMIUM").
Thank you for your reply, I may endeavor to simplify my formulas and load them from excel now.