Cubeset with topcount and several filter - problem

[jazpar]

Hi

As a newbi in using excel cube formulas I have a problem I hope someone could be helping with.

Issue:
Getting top 5 top- and bottomcount for Customergroup.
The count should respect:
Selected Company dimension (there are several companies 10-15)
Selected invoiceperiod dimension

Cubeset formulas:
Topcount
CUBESET("Sales","topcount(([Company].[Company].["&$C$10&"],[Customer].[CustgroupIdName].children),5,sum(([Company].[Company].["&$C$10&"], [InvoiceDate].[YearMth].["&$C$16&"], [Measures].[LineAmount])))","Top 5 Customergroup")
Bottomcount
CUBESET("Sales","bottomcount(([Company].[Company].["&$C$10&"], [Customer].[CustgroupIdName].children),5,sum(([Company].[Company].["&$C$10&"], [InvoiceDate].[YearMth].["&$C$16&"], [Measures].[LineAmount])))","Bottom 5 Customergroup")

Cuberankedmember formula:
CUBERANKEDMEMBER("Sales",Top5Kundegrp,$I3)
(I3 = the rank eg. 1)

Cubevalue formula:
CUBEVALUE("Sales",$J3, $C$16,"[Measures].[LineAmount]")
(J3 = the cuberankedmember; C16 = [InvoiceDate].[YearMth].[2014 - Jan])

Remarks:
[Company].[Company].["&$C$10&"] = [Company].[Company].[USMF]
[InvoiceDate].[YearMth].["&$C$16&"] = [InvoiceDate].[YearMth].[2014 - Jan]


Shown for the topcount:

Rank (typed in number Column I)	Customergroup (Cuberankedmember formula in Column J)	Lineamount (cubevalue formula in Column K)	Remarks
1	30 - Retail customers	2,052,524.00	Values and group from USMF company
2	10 - Wholesales customers	1,803,576.00	Values and group from USMF company
3	90 - Intercompany customers	328,130.00	Values and group from USMF company
4	90 - Intercompany customers		Zero value and group from company NOT selected
5	010 - Clientes região SE (Sudeste)		Zero value and group from company NOT selected

<tbody>
</tbody>

Could anyone help how to ensure:
When rank 4 and 5 are empty for the selected company – then don’t show those.
Why are they shown when in the cubeset I tried to filter on ONLY the selected company.

Thanks and have a nice weekend

 

[theBardd]

Can you post a workbook somewhere, there is quite a lot to try to mock-up?

 

[jazpar]

Can you post a workbook somewhere, there is quite a lot to try to mock-up?

If I knew how to I will. I have prepared an offline cube file and a spreadsheet. Could you tell me ?

 

[sheetspread]

https://www.box.net/ and https://www.google.com/drive/ are popular here

 

[jazpar]

OK have a google dev account, uploaded the files. But from here how do I fullfill the next step

 

[jazpar]

https://drive.google.com/file/d/0B0BiRy4rCjWUc0JXS0RQWF9PRnM/view?usp=sharing
https://drive.google.com/file/d/0B0BiRy4rCjWUNGJINEZhYVVJMzg/view?usp=sharing

 

[ImkeF]

Hi jazpar,
Your CUBESET is only filtered by the Company code, so it will always return all CustgroupIdNames within that CompCode, irrespective if they have sales or not. You can insert a non-empty-clause, that will reduce to non-empty values:

=CUBESET("Sales";"topcount(nonempty([Customer].[CustgroupIdName].children, [Company].[Company].["&$C$8&"]),5,sum(([Company].[Company].["&$C$8&"], [InvoiceDate].[YearMth].["&$D$13&"], [Measures].[LineAmount])))";"Top 5 custgroup")

You also need to Switch the Company & CustgroupIdName as you see in the bold section.

Then in your Top5-Table you wrap your formula in an iferror-clause and everything should look nice

 

[jazpar]

Thanks a lot, i Will try this at once??
Have a Nice Day imkef?

 

[jazpar]

Hi Imkef,

this worked out perfectly.
Do you know how a similary count could be made for total custgroup members with values for the company and selected invoice period.

In that way I might build a solution with no empty cells. Eg. in my test data there is only 4 custgroup as max for all companies with value. Some have only 2 or 3 custgroup. So for one company I could have a top 3 and a bottom 2. But the same formula would give me one of the members in both the top and the bottom list.

In advance thanks

BR

Jan

 

[ImkeF]

Hi Jan,
having difficulties in understanding your question.

With one table for Top5-Count and one for Bottom5, there will always be some CustGroups that appear in both tables as long as the selection returns less than 10.

So what do you mean with trying to "build a solution that has no empty cells"? Of course you could eliminate the Company-Filter in order to make the return-set bigger - but wouldn't that change the meaning of the report?

This would be it: =CUBESET("Sales","topcount(nonempty([Customer].[CustgroupIdName].children),5,sum(([InvoiceDate].[YearMth].["&$D$13&"], [Measures].[LineAmount])))","Top 5 custgroup")

Otherwise please help me understand what you try to achieve

Thanks, Imke