Author Topic: Top 10 Customers with Filter criteria  (Read 181 times)

Offline vincydza@gmail.com

  • Senior Member
  • ****
  • Join Date: Apr 2014
  • Posts: 57
  • Forum Citizenship: +0/-0
Top 10 Customers with Filter criteria
« on: 03 May 2019 09:59:38 am »
Hi,

The expression to retrieve top 10 customers based on revenue is as follows.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])

How can this expression  be tweaked to retrieve records exclusive for North America or Europe?

Please advise.

Vincent






Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,651
  • Forum Citizenship: +638/-10
  • Cognos Software Muppet
Re: Top 10 Customers with Filter criteria
« Reply #1 on: 03 May 2019 10:19:41 am »
Hi,

The expression to retrieve top 10 customers based on revenue is as follows.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])

How can this expression  be tweaked to retrieve records exclusive for North America or Europe?

Please advise.

Vincent

Hi,

Rather than using just [Revenue] in the expression, you can use a tuple() of Revenue and the desired Region member instead,

eg

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,tuple([Your Europe member],[Revenue]))

Cheers!

MF.
Meep!

Offline vincydza@gmail.com

  • Senior Member
  • ****
  • Join Date: Apr 2014
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: Top 10 Customers with Filter criteria
« Reply #2 on: 03 May 2019 10:36:44 am »
MF,

The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with  null values.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Vincent

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,651
  • Forum Citizenship: +638/-10
  • Cognos Software Muppet
Re: Top 10 Customers with Filter criteria
« Reply #3 on: 03 May 2019 11:21:07 am »
MF,

The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with  null values.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Vincent

Is this where there are fewer than 10 customers with a revenue value for a given market segment?

You could use a filter() function to weed these out if necessary

topCount(filter([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No], tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]) is not null),10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Or do you mean something different?

MF.
Meep!

Offline vincydza@gmail.com

  • Senior Member
  • ****
  • Join Date: Apr 2014
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: Top 10 Customers with Filter criteria
« Reply #4 on: 07 May 2019 02:21:58 pm »
something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.

If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola"  should appear on the report.

Vincent

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,651
  • Forum Citizenship: +638/-10
  • Cognos Software Muppet
Re: Top 10 Customers with Filter criteria
« Reply #5 on: 07 May 2019 03:47:27 pm »
something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.

If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola"  should appear on the report.

Vincent

Assuming this is a crosstab with Customer Name nested below Market Segment (rather than a list report), the expression to retrieve the Market Segment members would be [SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment?

Otherwise, the previous topCount() expression should be fine for your Customer Name members...

Cheers!

MF.
Meep!

Offline vincydza@gmail.com

  • Senior Member
  • ****
  • Join Date: Apr 2014
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: Top 10 Customers with Filter criteria
« Reply #6 on: 13 May 2019 12:12:27 pm »
thanks

 



       
Twittear