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

 

[jazpar]

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]

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