BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Actuality
Fluorite | Level 6

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.  

 

1 ACCEPTED SOLUTION

Accepted Solutions
TSBruce
SAS Employee

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.

crosstab_distinct_count.PNG

View solution in original post

3 REPLIES 3
Actuality
Fluorite | Level 6

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

TSBruce
SAS Employee

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.

crosstab_distinct_count.PNG

Actuality
Fluorite | Level 6

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 999 views
  • 1 like
  • 2 in conversation