Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this data:
LOAD * INLINE [
Country, Population
Portlandia, 1
Girlana, 2
Festonville, 3
];
LOAD * INLINE [
Country, Square Footage
Portlandia, 100
Girlana, 200
Festonville, 300
Bombast, 300
];
I want to see this table:
$Field | {Count of Distinct Members in that $Field} |
Country | 4 |
Population | 3 |
Square Footage | 3 |
I tried using an expression like this:
COUNT([$($Field)])
But it didn't work...
Notice I don't want this:
$Field | COUNT($Field) // COUNT of Tables in which that $Field appears |
Country | 2 |
Population | 1 |
Square Footage | 1 |
Use a pick/match combination.
I also tried this:
$(=COUNT([$($Field)]))
I'm not sure if the $($Field) is working how I expect, even this Expression doesn't produce anything:
='$($Field)'
And even if I make my own data, (rather than using System Field ($Field))...
LOAD * INLINE [
Field
Country
Square Footage
Population ];
...it doesn't work how I expect...
='$(Field)'
I can do simple concatenation:
'' & Field
or
'' & $Field
That's fine, but I want to treat each $Field value as if it were a "fld" so I can pass it to COUNT(...)
You can't change the expression per dimension line (though you can use a pick/match method to pick the right distinct field count per dimension value).
If you are just interested in the field distinct value counts, go to Setting - Document Properties - Tables.
Thanks -- I'm also interested in MAX() for each $Field. So it's a general problem. And it's *very theoretical*, I can think other ways around this... But I'm just curious.
As you say, "can't change expression per dimension line" -- I think you're right. At least, if I just use a plain Text Object, whose value is
*Edit*
=COUNT($(vField))
Where vField is
$(=$Field)
Then, *when I click on an (ONLY) specific $Field value*, the Text Object will show me the correct count; i.e. if I click on "Country" then the Text Object shows 4.
My brain must not be working, I thought the exact purpose of Dimensions vs Expression is to allow the Expression to be evaluated in the "context" of *each* Dimension (line)
This seems to work only if you select the field. May be somebody can expand on the idea:
=SubStringCount(Concat({1}DISTINCT $(='[' & $Field & ']'), '|'), '|') + 1
So I think it's easier to do this:
A plain Text Object, whose value is
=COUNT($(vField))
Where vField is
$(=$Field)
Then it works as in your example.
But how to get the COUNT to appear in a Listbox or Table -- for *each* Dimension?
Use a pick/match combination.
Just as you said -- must use Pick/Match. Quite ugly, but it works! Thanks.
And it's not so terribly ugly, once it "one-level" evaluates the string part, the result is just mapping each $Field to the correct expression.
Pick( Match($Field, 'Country','Population','Square Footage') , Count( DISTINCT [Country]), Count(DISTINCT [Population]), Count(DISTINCT [Square Footage]))
Have you used this technique elsewhere @swuehl?
You can use similar e.g. when you need aggregation with set analysis that looks at current dimension values.