cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

 The following article describes how to answer the following question: 
Which countries have sales for product A but not for product B

The solution is based on using the Filtered Measure plugin.

  1. Go over the instructions in the Technical Details of the plugin.
    Please follow steps 1-8.
  2. After understanding how to work with the plugin, In our example, we would like to know what is the Cost for countries that had sales in particular Categories but didn't have sales in other categories.
  3.  
  4.  

Here are the selected fields for this business question:

  • In the EC we will duplicate the Category field
  • On the dashboard we will create 2 filters out of both Category fields - Include Category and Exclude Category
  • Add a new pivot table and add 2 Rows: Country and CountryID
    * The countryID field is presented only for QA purposes

3. We will create 4 Values in the Pivot table:

* After creating values #1 and #2 save them as Starred Formula to be then used in value #3
  1. Filter Value #3 to include only values = 1

 

Community_Admin_0-1634205990897.png

 

  1. Hide the first 3 values and leave only value #4 visible
Community_Admin_1-1634205991062.png
  1. We can now interact with the two filters on the dashboard level and see how the countries presented are changing accordingly.

 

If we were to add other filters, for example: Date year = 2013, we would be answering the business question of: 

What was the Cost in 2013 for Countries that in 2013 had costs for product A but not for product B

 The SQL equivalent of this business question is:

select c.[Country ID], sum(c.[Cost]) [Total Cost]

from [Commerce] c

where c.[Country ID] in (select c.[Country ID]

                         from [Commerce] c

                         join [Category] ca

                         on c.[Category ID]=ca.[Category ID]

                         where ca.[Category]='Apple Mac Desktops'

                         and getyear(c.[Date])=2013

                         group by c.[Country ID])                          

 

and c.[Country ID] not in (select c.[Country ID]

                         from [Commerce] c

                         join [Category] ca

                         on c.[Category ID]=ca.[Category ID]

                         where ca.[Category]='Camcorders'

                         and getyear(c.[Date])=2013

                         group by c.[Country ID])

and getyear(c.[Date])=2013

group by c.[Country ID]

 

Community_Admin_2-1634206089857.png

 

That's it! 

 

Version history
Last update:
‎10-14-2021 03:13 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request