Extracting data, counting it, then returning it split

[ben.j]

Hi all.

I’m trying to display some data in a graph, and I’m struggling awfully. I’m sure it’s super simple and I’m just tired, but been at it for ages and can’t seem to crack it. Any help appreciated.

I have a data type which stores cancellations - it stores a fair few fields but all I’m interested in is counting how many instances of x reason there are, and returning it. It should return how many instances there are of each reason, split by a semicolon so that the graph can interpret each value.

Data Type & relevant fields. The above result should return 1; 1; .

I need to display the results as:

resultA; resultB; resultC;

for the graph to read each value.

I’ve been able to achieve this with the labelling aspect, it takes each item’s ‘reason’ as expected using the following expression:

I’ve tried a whole combination of expressions but to no avail. I currently can count each instance of a cancellation:

but this just counts how many ‘cancellations’ there are, not how many instances of each.

I hope I’ve explained this in enough detail, if anyone has any ideas please do let me know. Much appreciated.

[Jici]

Not sure exactly with your settings but did you consider using :group by?

[ben.j]

Thanks @Jici for the reply. This has certainly progressed the condition.

I can now differentiate the three different options (as is currently in the testing data):

The last hurdle now is to get this formatted correctly for the graph. I tried extracting as text (split by ; ) but this doesn’t work.

When I try to format as text, the data is lost.

Any ideas how to get the three different options into a

1; 1; 1;

format as required?

[Jici]

You are not using the groub by correctly.

For label, Search for status change request:group by reason’s reason
For values, Search for status change request:group by reason:count
You don’t need to format as and split by, you already have a list in both case.

[ben.j]

Thanks for your explanation @Jici , I’ve learnt something new!

Now works as expected. Very much appreciated!