Count Distinct problem

[consultant_pks]

Hello,

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!

TS

[Former Member]

Hello,

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.

[sameer002]

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