Custom Dimensions: Sales Except with "X" Tag

josephmckeown

Sometimes we want to look at all Sales except for the Products that contain a certain tag.

Like Inventory considerations, tags is not a good way to filter out products. This is because products with multiple tags will remain on reports even if one of their tags is filtered out.

The best way to filter out a collection of products is by

• category
• brand
• cost
• an identifier within one of the unique fields (Description, IDs, etc)

But sometimes we don't have that luxury, and tags are what we need to use.

We can isolate the Sales data we're looking for by using a Custom Dimension and Calculation.

Custom Dimension for is Tag?

To begin, we'll open a Sales Report, go to our Custom Fields, and create a new Custom Dimension:

Now this will be a very simple calculation. We'll search for "Tag"

Here it is!

Then click on the tag, pushing ${tags.name} into the code editor.

Next we'll enter equals...

...and between quotation marks, we'll add the name of the tag we're looking for.

Let's say it's "consignment",

We'll save the Custom Dimension, run the report, this will push "Yes/No" onto our data:

Lines labeled with the "Yes" are the totals with those tags. Lines labeled "No" are the totals not of those tags.

But this is not the end.

The problem with ending the report here, is that items with multiple tags are not filtered away from the report. So we'll need one or two more steps.

Next Step: Pivot and add Row Total

So next, we're going to pivot on the Custom Dimension that we just created....

And we're going to select "Row Totals" as an option...

Let's run the report

We'll see how the "Totals" are not necessarily No+Yes, as the same product may be present in both "No" and Yes"

However, we could isolate the complete value of "No" by subtracting the "Yes" from the Row Total!

Final Step: Total minus "Yes"

As a final step, let's go to our Custom Fields, and add a new Table Calculation...

And we'll set up a "Pivot-Where" function....

Asking our Custom Dimension called "Tagged Consignment"...

where valued as "Yes"...

...to return the Total dollars

(I've noticed recently an error displayed when a pivot-where argument is said to be incorrect, when in fact it is. Forge ahead. If it's not working, it will tell you on the data)

Looks good

So now, let's edit the calculation...

By adding the row total prior to the calculation, and subtracting

So that when we save:

We get a much more meaningful number!

Final Step

As a final cosmetic step, we could hide the raw data of "Sale Line Total" from visualization

So that our visualized report becomes just the meaningful summary that we want!

Calculation Home

Spoiler Alert!

We used the following calculations in this report:

Tag is what we're looking for (Custom Dimension):

${tags.name}="consignment"

Subtract "Yes" from Row Total (requires the name of your above dimension), (Table Calculation)

${sale_lines.total_sales_no_tax:row_total}-

pivot_where(${tagged_consignment}=yes,${sale_lines.total_sales_no_tax})

22455