Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a fields named 'A' untill 'Z'. In my chart the dimension is $FieldNo and I want one expression so that when $FieldNo = 1 -> sum(A) ; $FieldNo=2 -> sum(B), etc. I tried making an expression while loading, being 'sum(A)', etc, but that works just for one single value.
I want it to work what in Excel the function INDIRECT() does!
=FieldValue($Field,1)
or
=sum(FieldValue($Field,1))
I am having trouble understanding the exact result you needed but this might help?
=sum(pick($FieldNo,A, B, C))
or more generalized
=sum(pick($FieldNo, $(vFieldList)))
the second option requires you to define the vFieldList variable as follows:
SET vFieldList = =concat({1} $Field, ',');
Note two equal signs. The = must be part of the variable def.
-Rob
A little explanation might help:
I load about 50 KPI's from an Excel-format for every location and every week. Then I want to represent for a selected week the data as follows: dimension 'location' in the x-direction of the table and the KPI's in the y-direction. To prevent that I have to write a lot of if-statements to get the KPI's in the y-direction, I was trying to use $Field and/or $FieldNo as dimension and just one Sum-statement which is based on $Field or $FieldNo to determine what field should be calculated. I thought using the value of $Field as the fieldname in sum(). But maybe there is some other way to achieve this?
I hope I made it more clear.
Liesbeth