Skip to Content

Count Distinct problem

Oct 31, 2016 at 11:58 AM


avatar image


Task is to make Count distinct measure (in moreless open query - 100 attributes which can be used for filtering, or drill downing with no restriction ) of distinct Customers.

We have made Calculation View on default CV. There in aggregation node we made Calculated Columns - counter on CUSTOMER_ID attribute.

Then we tried to use this CV as a "infoprovider" for Composite provider and bulid query on it. But CD values werent correct when user use filter on attribute he didnt use for row/column display. It was aggregated.

So we tried Transient provider instead of Composite. It works better (but still with some problem - most of them were resolved by turning of caching in RSRT transaction) but there is still problem with summarizing in some cases.

Example - There is default (soft) filter on year and year is deafultly displayed as a row. If we turn out filter on year and get it out of displeyed attributes, there are like 2 milion distinct Customers. BUT then when we filter month (f.E. 3,9,12) there is nearly 6 milion of "distinct" client shown then. When we put year back into diplayed attributes, there is about 2 milion in each year again. Which mean, that for some reason it sum values on years instead of taking distinct values through all those years.

Please can someone suggest some solution for this? (I have read some topics about transparent filters, but we cant be sure which attribute will be used as a display one, and which as a filter)

I appreciate your answer!


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Sameer Ailawadi Apr 10, 2017 at 03:14 AM

I have the same problem. Appreciate if someone could help.

10 |10000 characters needed characters left characters exceeded
Mohamed Ramees Raja Abbas Manthiri May 19, 2017 at 05:55 AM


I am not sure about how to achieve this in HANA modelling . But we faced a similar issue in HANA sql .

select count ( distinct col1 ) over (partition by col2,col3) as cnt from table ;

This above query works in oracle but doesn't work in HANA because we cannot use distinct within the aggregation in HANA.

To achive this we gave the fix as below

select sum( case when rk=1 then 0 else 1 end)+1 over ( partition by col2,col3) as cnt from

(select rank ( ) over (partition by col2,col3 order by col1) as rk from table)A ;

Hope this helps in finding the solution in modelling.

10 |10000 characters needed characters left characters exceeded