Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am currently working with some data which includes a list of multiple items in some rows. I would like to count the occurrence of each item in a bar chart, without having to create individual measures for each bar.
For example, my table looks like:
List |
I ate an apple, I read a book |
I read a book |
I ran a race, I ate an apple |
I ran a race |
I ate an apple |
I read a book, I ran a race |
So "I ate an apple" = 3 ; "I read a book" = 3 ...
I know that I can create individual bars using a measure like Count({<List={"*I ate an apple*"}>} List).
However, what I would like to do is just have to use one dimension and measure to accomplish this. Is this possible?
I almost had it with the following:
Dimension = Valuelist('*I ate an apple*','*I ran a race*','*I read a book*') and
Measure = Count(If(WildMatch(List, Valuelist('*I ate an apple*','*I ran a race*','*I read a book*')), List))
But the asterisks then displayed in the chart. Any ideas?
Hello Michelle,
My advice is to create a second table by splitting the multiple records of list into rows.
Yet, you still can achieve what you want in the chart expressions as:
dimension: Valuelist('I ate an apple','I ran a race','I read a book')
measure: pick(match(Valuelist('I ate an apple','I ran a race','I read a book'),'I ate an apple','I ran a race','I read a book'),
Count(If(WildMatch(List, '*I ate an apple*'), List)),
Count(If(WildMatch(List, '*I ran a race*'), List)),
Count(If(WildMatch(List, '*I read a book*'), List))
)
Regards,
Hello Michelle,
My advice is to create a second table by splitting the multiple records of list into rows.
Yet, you still can achieve what you want in the chart expressions as:
dimension: Valuelist('I ate an apple','I ran a race','I read a book')
measure: pick(match(Valuelist('I ate an apple','I ran a race','I read a book'),'I ate an apple','I ran a race','I read a book'),
Count(If(WildMatch(List, '*I ate an apple*'), List)),
Count(If(WildMatch(List, '*I ran a race*'), List)),
Count(If(WildMatch(List, '*I read a book*'), List))
)
Regards,