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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 753 views
  • 1 like
  • 2 in conversation