Last n entries

[sebastian (Sebastian Soller)]

Hi,

i am trying to create a table, which shows the last 3 entries for each category.

Is there a way to do this inside a Metric set, where a user could do it, or do i need to prepare a DataCube for this function?

`Thanks in advance.

[derek.sell]

Hi Sebastian,

I believe you could approach this by using a top/bottom transform at the cube level. You may also see the Dundas BI documentation as to how to set it up for a metric set.

https://www.dundas.com/support/learning/documentation/analyze-data/displaying-top/bottom-records

[abigail]

Hi Sebastian,

One method you could try is to use a cube and transform the cube using Top/Bottom transform with grouping enabled. You can configure it as shown below:

In the meantime, I will discuss this with the R&D team and let you know if there is an alternative solution.

[abigail]

Hi Sebastian,

You can also show the N last entries for each category at metric set level and have the user to choose the number of records dynamically or you may set it statically (e.g., in your use case, 3) in the formula.

Please see below for a sample of how to do this:

1. Create your metric set including the Category, Date, and measure column

2. Create a Formula Visualization using “ LAST ” SUBSET formula from subset formulas as below:

LAST(d0,s0,Alignment)

i.e. LAST($measure$,$no. of entries$, $date$)

The highlighted part is the formula visualization showing the last N entries (here N= 3)

• $lastentries$ is a Parameter Placeholder by which you can change the number of entries you want to see through a single number filter on the dashboard. If you want to keep it static and always see only the last three entries, instead of $lastentries$ placeholder, you can just hard code it to 3 in the above formula.

Note : While entering the formula in the formula bar please click on any of the data values for the auto-fill feature, and not on the column headers. For e.g., click on any of the dates and not on the column header ‘Created Date’

3. Edit the formula measure from the data analysis panel of the formula visualization and click on the parameter placeholder in the formula bar (in this case $lastentries$ and set the ‘Parameter Type’ and ‘Default Value’ as shown below:
   

4. Create a Single Number Filter control and uncheck all the default bindings, and then expand the Formula Visualization metric set and check the parameter placeholder (here $lastentries$ )

5. Hide the original table from table Properties Panel -> Layout -> enable ‘Hidden’
6. Check the functionality in View Mode.