Count combination of multivalue field

[IRHM73]

Hi, I wonder whether someone can help me please.

I'm using the query below to extract the different actions performed for each submission by detail.Id

`submissions_wmf(Submission)` detail.isManualChange=true NOT ( detail.changeType=ChangeBank OR detail.changeType=ChangeBIK OR detail.changeType=ChangeOtherIncome OR detail.changeType=ChangeSocialSecurityBenefit OR detail.changeType=HaveBenefitsEnded OR detail.changeType=HavePartnerBenefitsEnded) 
| stats count list(detail.changeType) as ChangeType by detail.id
| table ChangeType count

The query works find and extracts data as per the attachment1

But I'd like to extend this by adding another total which counts the number of times the combination of values in the ChangeType Column exist.

So using the attachment as an example.

Where Change A and Change B exist together this would be a count of 2.

I've looked at streamstats and evenstats and also changed the values to a string and count this, but I can't pull both totals together on the same table.

I just wondered whether someone could look at this please and offer some guidance on how I may go about this.

Many thanks and kind regards

Chris

[kyaparla]

This should work..

\submissions_wmf(Submission)` detail.isManualChange=true NOT ( detail.changeType=ChangeBank OR detail.changeType=ChangeBIK OR detail.changeType=ChangeOtherIncome OR detail.changeType=ChangeSocialSecurityBenefit OR detail.changeType=HaveBenefitsEnded OR detail.changeType=HavePartnerBenefitsEnded)
| stats count list(detail.changeType) as ChangeType by detail.id | eval ChangeType=mvjoin(ChangeType,"_") | eventstats sum(count) as combinationcount by ChangeType`

[IRHM73]

Hi @kyparta. Thank you for this.

Unfortunately why I tried this it initially doesn't work in the format you've posted.

I then removed the \ and the query then it creates the same problem I'm having in that for every changetype the "combinationtotal" is the same value e.g.

ChangeType CombinationTotal
Change A 2
Change A 2

I suppose what I'm looking for is a subtotal for each change in the string value if that makes sense.

Many thanks and kind regards

Chris