topic Re: Distinct count of users based on user type, in a cross tab, based on grouping displayed in SAS Visual Analytics

Distinct count of users based on user type, in a cross tab, based on grouping displayed

Actuality — Wed, 15 Nov 2023 00:00:22 GMT

I am looking for a way to display the distinct count of users based on the user type, in a cross tab, based on grouping displayed as a hierarchy is expanded.

A user could be one of two types - and there needs to be a column for each type, for the distinct count of users of that type. Say, Type A, and Type B.
Each user has a unique identifier and a type recorded, and can have multiple rows of data for each level of the hierarchy.
The hierarchy in the cross tab, is: Financial Year -> Financial Year Quarter -> Residential State
When the cross tab displays Financial Year, it is to display distinct count in columns for Type A, and Type B, for the Financial Year.

Then, when hierarchy expands to Financial Year Quarter, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter.
Then, when hierarchy expands to Residential State, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter -> Residential State.

Re: Distinct count of users based on user type, in a cross tab, based on grouping displayed

Actuality — Tue, 21 Nov 2023 00:41:12 GMT

Is this possible? Sounds like it should be simple, but I haven't worked it out yet.

Re: Distinct count of users based on user type, in a cross tab, based on grouping displayed

TSBruce — Tue, 21 Nov 2023 12:53:55 GMT

You should be able to do this by creating a distinct count on your unique identifier. To create a distinct count, right click on your unique identifier and select New Calculation. Select Distinct Count for your type to create a new aggregated measure. You can then put your hierarchy on rows, the Type data item on columns, and select your new distinct count as the measure. A screenshot using sample employee data is below, but this should give you an idea of what it would look like.

Re: Distinct count of users based on user type, in a cross tab, based on grouping displayed

Actuality — Thu, 30 Nov 2023 03:26:26 GMT

Thank you @TSBruce I am able to display distinct user count based on hierarchy as per your guidance.