Count number of clients by number of products used, stacked by status

[DSRScape]

My dataset records a status of Complete or Active against a Product for each Client. Clients can have none or multiple products recorded.

ClientRef	Product	Status	ReportDate
CL005			31/12/2018
CL006	AAA	Complete	31/12/2018
CL006	BBB	Complete	31/12/2018
CL006	CCC	Active	31/12/2018
CL007			31/12/2018
CL008	DDD	Active	31/12/2018
CL009			31/12/2018
CL011	BBB	Complete	31/12/2018
CL011	DDD	Active	31/12/2018
CL014			31/12/2018
CL015	AAA	Active	31/12/2018
CL015	DDD	Active	31/12/2018
CL015	BBB	Complete	31/12/2018
CL017	EEE	Active	31/12/2018

I need to represent in a stacked bar chart how many products clients have, split by Status, with the dimension ranging from 0 to the number of products that exist. I got it working (see attached) but had to restructure my dataset and am unable to recreate it.

Hope someone can help!

Thank you,

David

[JustinDallas]

Do you mean something like this?  I tried attaching the qvf, but it keeps saying "The contents of the attachment doesn't match its file type."

[DSRScape]

Not quite - this is what I'm attempting to recreate, so e.g. a total of 12 clients have engaged with 6 products giving 72 statuses of Active or Complete. Just over 300 have not engaged with any products:

Hope that makes sense and thank you for your time.

David

[JustinDallas · Accepted solution]

Better yet, do you know what the test data SHOULD look like?  When I make my dimension "Aggr(COUNT( DISTINCT Product), ClientRef )" and add another dimension of "Status" I get this.

But to be honest, me and AGGR aren't very good friends so I'm not 100% on this.

[DSRScape]

That is spot on for that sample and spot on for my actual app. I clearly need to make friends with AGGR!

Thank you so much for your help Justin.

David