OBIEE Report Based on Member and there sales%

Nikhila reddy

I would like to generate a report of this kind. In which I will select a month from the dashboard prompt and the respective graph should display Top 1% of the customers who contributed for highest amount of sales and vice versa for the rest.

asim cholas

What if , you have less than 100 customers? say for example just 20 customers?How will u calculate top 1% customers? Top 1% will be less than 1

May not work. Will check in the system and will update.

Nikhila reddy

Yes, Your point is correct but in my case that wont happen.

can you please explain the previous comment which you have posted.

Thanks

asim cholas

it could fail when the number of customers are less than 100. So it is not the correct solution

Nikhila reddy

Hi asim

Here we got millions of customers so that wont be a case. You have any solution for this.. please help me out.

Thanks

asim cholas

I will have to try it in my environment. Will keep you posted.

asim cholas

Apply this formula in Filter and check if you will get the top 1%

RANK(sum("SALES_AMOUNT" by ""CUSTOMER_CODE"))<"CUSTOMER_CODE" by 'None')*0.01

add four columns in criteria so that you can validate the value

Column 1

RANK(sum("Sales_AMOUNT" by "CUSTOMER_CODE"))

column 2

Customer_code

column 3.

For the total sales amount

sum("Sales_AMOUNT" by 'None')

Column 4

For Top 1% of customers

count(distinct "CUSTOMER_CODE" by 'None')*0.01

Column 5

Dummy column for data. Each criteria add the corresponding value.

Check the data and let me know.

add another criteria(combine similar request and paste the first criteria and apply filter like below for 1 to 10 % customers.

RANK(sum("Sales_AMOUNT" by "CUSTOMER_CODE")) between (count(distinct "CUSTOMER_CODE" by 'None')*0.01 )+1 and (count(distinct "CUSTOMER_CODE" by 'None')*0.10)

Third Criteria Filter

RANK(sum("Sales_AMOUNT" by "CUSTOMER_CODE")) between (count(distinct "CUSTOMER_CODE" by 'None')*0.10 )+1 and count(distinct "CUSTOMER_CODE" by 'None')

asim cholas

Let us know the output and kindly mark helpful correct answers.

Nikhila reddy

Thank You so much for this information,.

i have created 2 Criterias and 1 st criteria has Top 1 % and second criteria has Top 2 to 10% but when am trying to do union all between this two criterias, am unable to get  second criteria results,

asim cholas

save as test report and delete the first criteria and see what is the result. show me the first criteria and second criteria. all the columns and filters. especially what is the formula inside amount column.

Nikhila reddy

Thank you. It is working now.