Distinct counter using counter option in SAP HANA

Author Bio

MP Reddy is a Principal Consultant at NTT DATA from the SAP Analytics Practice.

Summary

Business want to identify what is the count distinct products brought by each customer.

In this article let’s look at how to calculate distinct count for any attribute (column) in HANA with the help of one of the new feature available ‘Counter ‘.

Solution:

In this kind of requirement, just count of products would give us wrong results because customer might have brought same product multiple times. We should be able to calculate distinct count of products to get right solution.

This can be achieved with the help of ‘Counter’ feature available in both analytic and calculation view in SAP HANA.

Step #1:

To create new counter, right click on calculated column folder ->new counter.

Step #2:

Enter the technical name and description for new counter. We need to select on which column we would like to have distinct count. In our case, it is “PRODUCT CODE”.

Step #3:

Click OK to finish the creation process and see the new count column listed under ‘calculated column’ folder. To test our final results, we have also created an additional calculated measure which just does count of PRODUCT CODE.

Step #4:

Activate the analytical view and click ‘data preview’ to check the data. When we check the distinct products (green) for each customer, we can easily identify the difference count when compared to actual product count (blue).