The Power of Numeric Filters

See this article in Bi4 WorkBooks     

Text and Numeric Filters

Bi4Cloud provides filters to allow you to segment your data. These filters can operate text values in the data and on the numeric values of metrics. 

The filter panel displays when you press the filter icon   and the  panel below is displayed. The top section are the text filters and at the bottom are the numeric filters.

Screen_Shot_2022-07-13_at_5.45.00_pm.png


You create a Numeric filter by  clicking Add a Numeric Filter and choosing the type of metric to filter. 

Here is a sample of the Numeric filters from the Sales Vs All Inventory mapping.

Screen_Shot_2022-07-13_at_11.28.30_pm.png

Note that there  are two styles of metric filter. Ones based upon filtering  at the Source of the data and ones that filters based upon the Results query.

The Source data is the lowest level tat a report goes to and in Sales Vs All Inventory  this level is the Item and it's Location which is shown below. This example is filter for 4 Items.

No Numeric Filters Applied

Screen_Shot_2022-07-13_at_11.28.59_pm.png

The above shows a column for Last Sold Wks and  On Hand Stock for each location, grouped by Item. Below we Analyse by is Item and we see the On Hand as the sum of the Item locations but notice the Last Sold Wks in the minimum of the Item Locations Last Sold Wks. It's not the sum of the Last Sold Wks because it needs to be the minimum of the last weeks when it was sold. That is what the arrows highlight in the above shot.

Screen_Shot_2022-07-13_at_11.29.10_pm.png

Using Numeric Filters

Let assume you want to retire ( set InActive ) all stock that you have not sold for the last 6 months (27 weeks) and that has 0 stock on hand.

Let's choose the qty_onhand (source) = 0  and apply this in Analyse by InvoiceStockLocation, then only locations with zero stock are displayed.

Screen_Shot_2022-07-14_at_12.31.23_am.png

All Locations with zero stock are displayed - notice the on hand sums for the item sub-total are zero because the locations with non-zero stock have been filtered out.

Screen_Shot_2022-07-14_at_12.31.57_am.png

Now add in weeks_since_last_sold (source) > 27 

Screen_Shot_2022-07-14_at_12.32.09_am.png

and locations that have not sold for 27 weeks are filtered for.

Screen_Shot_2022-07-14_at_12.32.21_am.png

Now switch to Analyse by Item

Screen_Shot_2022-07-14_at_12.32.33_am.png

Great, but this, however, is the wrong result if we intend to use this to retire stock because with reference to the original screenshot No Numeric Filters Applied we know there are locations with stock that have sales < 27 weeks ago but these are excluded because the locations have non-zero stock.

This is where the Result filters become vital. These filter on the results of the Analyse by dimension. Lets change the filters to the equivalent Result filters.

Screen_Shot_2022-07-14_at_12.32.45_am.png 

If we applied this to the Analyse by InvoiceStockLocation dimension then the result would be the same as Source filters because that is the lowest or source dimension. However when these filters are applied  to the Analyse by Item dimension the aggregate values for the Items are used for the filters. This shows there are no Items that have On Hand  = 0 and Last Sold Wks > 27 which is the corrrect result.

Screen_Shot_2022-07-14_at_12.32.57_am.png

Generally it is best to use the Result Numeric filters because these filter on the dimension being displayed.

Source filters do have their uses however. An example would be to eliminate give-away or small value lines Sales Analysis. The Analyse Sales Mapping  has the lowest transaction level or source level of the Invoice Line Detail. So to eliminate the small value lines you can use a source numeric filter on the invoice line amount < $1

Was this article helpful?
0 out of 0 found this helpful

Articles in this section

Ask the BI4 Community
Need help using this report or feature?