Hi,
- I have a data table in which the field '%FieldList' contains a list of fields names existing in my data model.
For example, if my datamodel has 3 fields Account, ClientID and City, the %FieldList will simply have 3 rows as following :
concat(%FieldList,',') = Account,ClientID,City
- To simplify my problem : I want to build a straight table with one row per fieldname and to use the name of the field in the current row as the fieldname parameter in a count() function into the expression.
- To sum it up : I want the text value of my dimension to be a parameter of the expression.
Let's say I have 2 accounts, 10 clientID and 3 cities.
I am trying to build a straight table such as :
- Dimension 'Field' : %FieldList
- Expression 'Count' : =count(DISTINCT %FieldList)
The result would be :
| Field | Count |
|---|
| Account | 2 |
| ClientID | 10 |
| City | 3 |
For now, the turnaround could be using a pick function with the list of each possibility (which could be dynamically written into a variable) as per below :
=pick(match(%_DimList,'Account','ClientID','City'),
count(DISTINCT Account),
count(DISTINCT ClientID),
count(DISTINCT City))
But I wonder if there is a smarter & more efficient way to do that.
Thanks for your help !
Emilien
