Loyalty Points Redeemed by GL - Date (and - Description)

This is a Club Prophet Executive Report v4 – Power BI or PBI.

Note: Samples are using demo data and do not reflect real-world data.

 

Note: These two reports are identical other than the rows and columns are swapped.

 

Loyalty Points Redeemed by GL – Date

This is a Matrix report that shows the number of Loyalty Points redeemed across defined categories (GL Code, Sale Category, etc) for the date range provided.  A Matrix report is a multi-dimensional Drill-Down type of report that you can drill-down by both (or either) row and column. 

So, in the example about the From-To date “slicer” is set for 1/1/2020 – 12/31/2020.  The current date is 7/6/2020 so we are really only looking through July 6.

The visual at the default top level is showing the year 2020 as the row along with the total.

Then for each row of General Ledger (GL) Description, we show the number of Loyalty Points Redeemed for each GL Code (GL Code – GL Description) and the total points redeemed.

In other words:

In 2020 there were 200,750 points redeemed for products assigned to GL Code: 9010 with the GL Description: 9010 – Green Fees for a total of 200,750 points redeemed since January 1, 2020.

Navigating the Matrix

If you select the visual by clicking it, you will notice that the Drill-Down control is a little different as it now has a selection to drill on the Rows and Columns.

Let’s start by selecting Rows which is the default selection and select the Down-Arrows.

Just like a typical Drill-Down, the report now shows the data but now by month for the given date range.

Select the Down-Arrows again and it will show by day of the week.

Select the Down-Arrows again and it will show by the date.

Note the down arrow is now disabled or grayed out so there are no more rows to drill down.

Now use the Up-Arrow to work your way back to the top until the up arrow is grayed out.

This is where it gets interesting! Let’s change the Drill On from Rows to Columns and repeat the Drill-Down routine using the down-arrows circled below:

On the first drill-down, you go from showing the GL Code - GL Description to GL Description – GL Code; so, you flipped the order which is subtle in this example:

On the next drill-down, you go to Sale Category:

On the next drill-down, you go to Product Description:

Note: You can scroll right for additional data.

On the next drill-down, you go to Customer Name:

On the next drill-down, you go to the customer Account Number:

And one more drill-down, takes you to the Sale number where the points were redeemed:

You are now at the bottom of the column drill-down, and the down arrows are now disabled. 

So now, you can drill-down to a combination of Rows and Columns and mix and match any way you like.  In this example, the drill-down on the Rows and Columns exposes which customers redeemed points by day of the week over the given date range:

Alternate Views

There are a couple other controls worth visiting here for alternate views of the report.

  • The Pitchfork control.

  • The Plus Sign control.

Scroll back to the top of the report using the up-arrow as needed.  Here we will select Rows and use the Pitchfork control which is circled below.

The result is that you now see a stepped version of the report:

You now see that the top row, which is 2020 in this case, is kept on the report with the next level indented.  You can continue this pattern all the way to the bottom of the hierarchy.  However, if you are only interested in on datapoint, you can also use the Plus Sign control.  Doing this for June shows this:

The Pitchfork control, while available for columns, does not seem to work as well so you probably only want to use if for Rows of data.

Keep in mind there is a report called Loyalty Points Redeemed by GL – Desc that flips the rows in columns so if you want to see the GL, Category, Customer, etc. data in this format, just use the other report as those fields are switched from the columns to the rows.

FILTERS

There are various filters you can use to narrow down the data.  Filters are the gold background controls as well as the Filters right-hand sidebar.

 

Tips

  • Filters which are the gold back color controls on the report allow you to pick from a list.  Hold down the Ctrl key on the keyboard to select multiple items.
  • If you want to search for a particular value, use the filters in the right pane menu.  They will have a search box in addition to a list of the items.
  • Use the Advanced option in the right menu panel for advanced search options.
  • Use the three dots […] in the top right of a visual for more options like Export data.

  • Use the Help button to open the user guide for any given report .