Expression Counting in pivot table

[#1]

Hi,

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.

Thanks

B.

[#2]

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?

Thanks in advance

[nagaiank]

It is not clear what you want to see in the RowNo dimension. If you are interested in displaying the number of rows giving the quantity, sales, then you may do the following:

- Remove the dimension RowNo()

- Add an expression Count(Qunatity) to the existing three expressions.

You will get this pivot table:

[#4]

Hi Krishnamoorthy and thanks for answering,

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.

[jonathandienst]

Hi

It should be enough to include the expression number and/or expression name in the dimension list. That would take care of the dimensions. For the expressions, you could use something like

=Pick(ExprNo, expr#1, exp#2, expr#3)

Where ExprNo is the expr number in the dimension, #expr1-3 are the actual expressions.

You can construct the Pick(...) expression manually or automatically from the expressions table.

Hope that helps

Jonathan

[jonathandienst]

Here is a quick and dirty example of what I mean.

If you want expression number rather than name, change the dimension Name to ExprNo. (Or add it as another dimension)

[#7]

Thank you Jonathan and sorry for my late reply,

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.

B.