# Cubeset with topcount and several filter - problem



## jazpar (May 9, 2015)

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)Remarks130 - Retail customers        2,052,524.00Values and group from USMF company210 - Wholesales customers1,803,576.00Values and group from USMF company390 - Intercompany  customers328,130.00Values and group from USMF company490 - Intercompany  customersZero value and group from company NOT selected5010 - 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 (May 9, 2015)

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


----------



## jazpar (May 9, 2015)

theBardd said:


> 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 (May 9, 2015)

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


----------



## jazpar (May 9, 2015)

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


----------



## jazpar (May 9, 2015)

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


----------



## ImkeF (May 12, 2015)

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 (May 12, 2015)

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


----------



## jazpar (May 12, 2015)

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 (May 12, 2015)

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


----------



## jazpar (May 9, 2015)

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)Remarks130 - Retail customers        2,052,524.00Values and group from USMF company210 - Wholesales customers1,803,576.00Values and group from USMF company390 - Intercompany  customers328,130.00Values and group from USMF company490 - Intercompany  customersZero value and group from company NOT selected5010 - 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


----------



## jazpar (May 12, 2015)

Hi
You are right - I Will try to clarify: what I want is a total count of customergroup for the specific company with values for the specific period.

So if 10 customergroup total for Company x, but only values for 7 of these in specific period then count should be 7.
Br
Jan - and thanks


----------



## ImkeF (May 12, 2015)

Hi Jan,
am I translating this right: “Forget the Tops and Bottoms, just give me the non-empties”? 

These cubeformulas become pita sooner or later (/are made for experts only). In theory this should be it:

=CUBESET("Sales","nonempty([Customer].[CustgroupIdName].children","Test",2,"([InvoiceDate].[YearMth].["&$D$13&"], [Measures].[LineAmount]))")

But at least it doesn’t work on my computer (german excel, English MDX-notation).

That’s why I’m using KISS-method: Create “helper pivots” that create the lines which your side tables will relate to. First advantage: They will only include non-empties.

So how to: on a separate worksheet:  Insert – PivotTable – Use an external data source – Choose connection – “ThisWorkbookDataModel” in Tables or “Connection in this workbook” in connections will show your cube (sales) – choose

Then you create you helper pivot: Drag CustGroupIdName into Rows and LineAmount into Values.

Then connect to the Company-Slicer and YearMth (or create). This will give you the selection of your CustGroups with relevant amounts.

In order to relate to them by your nice “non-pivot-table” on your main report, you replace the “CUBERANKEDMEMBER”-function by a CUBEMEMBER-function: 
=CUBEMEMBER(“ThisWorkbookDataModel”,”[Customer].[CustgroupIdName].[“&FirstRowCellOfYourHelperPivot&”]”)

The CUBEVALUE can basically stay the same, acutally you can skip the reference to the date, as this will be included in your helper Pivot already: 
=CUBEVALUE("Sales",$E4,"[Measures].[LineAmount]")

You can find an example of this method here: Cubeformeln - Dynamische Berichte Erstellen


----------

