[Feature proposal] Allow filtering by "Top" or "Bottom" on rows and columns

posted on February 28th, 2020

Answered

Projet TCD asked on February 28, 2020

Hello,
This is a feature proposal (not request) that could be nice to have in Flexmonster's pivot table.
Existing feature:
As of now, it is possible to filter values with a query objet on criteria top ou bottom (https://www.flexmonster.com/api/value-query-object/).
However, these criteria do not exist in other query objects (Number, String, Date, Time).

Proposed feature:
Adding support of "top" and "bottom" conditions on Number, String, Date and Time query objects.

Number: X top/bottom values
- e.g.: { "top": X }
String: X top/bottom values (by alphabetical order)
- e.g. { "top": X }
Date: Values corresponding to X latest/earliest "day", "week", "month", "quarter" or "year"
- This filter could return more than X values if "week", "month", "quarter" or "year" are selected
- e.g. { "top": "Xy" } with "Xy" being:
  - "Xd" – X days. Example: "5d"
  - "Xw" – X weeks. Example: "5w"
  - "XM" – X months. Example: "5M"
  - "Xq" – X quarters. Example: "5q"
  - "Xy" – X years. Example: "5y"
Time: Values corresponding to X latest/earliest "second", "minute", "hour" or "day"
- This filter could return more than X values if "minute", "hour" or "day" are selected
- e.g. { "top": "Xy" } with "Xy" being:
  - "Xs" – X seconds. Example: "5s"
  - "Xm" – X minutes. Example: "5m"
  - "Xh" – X hours. Example: "5h"
  - "Xd" – X days. Example: "5d"

Goal of the feature:

Allow more dynamic filtering of rows and columns: the same filtering result can be achieved by manually checking/unchecking values in the row/column filter, but since the members themselves are stored in the filter, it means it won't necessarily be correct when data is added/updated/removed.
Allow display of most recent data available when using dates as a dimension, even when the data is in the past (or at an unknown period): again, the same result can be achieved by filtering dates (greater than, last X days, ...) but they rely on specific dates to work so they need to be changed every time there is a change in the data.

Best regards,

7 answers

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ February 28, 2020

Hello,

Thank you for reaching out to us.

Your suggestion on adding such a feature sounds reasonable, and we have added the request on the implementation of the described functionality to our backlog.

We will keep you posted with any updates on this point.

We are grateful for sharing your ideas with our team. Do not hesitate to contact us in case any additional proposals or questions arise.

Best regards,
Illia

Public

Projet TCD ⋅ May 15, 2020

Hello Flexmonster team,
Is there any new information on this feature for the near future?
I'd also want to add the suggestion, for the sake of completeness, that this feature could include the "not_top" and "not_bottom" conditions for all query objets (i.e. the opposite of the proposed feature above).
Thank you and best regards.

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ May 15, 2020

Hello,

Thank you for writing to us and sharing your ideas.

Our team would like to kindly inform you that your request is placed to our backlog. However, our roadmap is full at the moment. We have complemented the request with your latest idea about "not_top" and "not_bottom" filtering.

We will notify you as soon as something is changed on this point.

Do not hesitate to contact us in case additional suggestions arise.

Best regards,
Illia

Public

Bill Kaper ⋅ October 12, 2023

I just want to +1 this feature request. I'm doing some as of date reporting and am providing the as of date to the user to filter on, and I would like to automatically be able to set the most recent date being defaulted to filter when the report loads and the rest of the dates not. Then the user can choose what date they look at if they want to look at a previous date.

Public

Maksym Diachenko ⋅ Flexmonster ⋅ October 13, 2023

Hello, Bill!

Thank you for your question.

After analyzing your request, it looks like similar functionality can already be achieved in Flexmonster. We recommend using the date query filter to show records corresponding with the most recent date. In the code snippet below, the filter only shows the records with today's date:

filter: {
    query: {
        current: "day"
    }
}

Kindly note that the "date string" or `"datetime" data types should be set in the mapping as a prerequisite for this approach.

You are welcome to check the JSFiddle example: https://jsfiddle.net/flexmonster/tczf2m5o/
Also, you can refer to the list of all available filter conditions if necessary: https://www.flexmonster.com/doc/filter-by-condition/#conditions

Please let us know if the suggested approach works for you.

Best Regards,
Maksym

Public

Bill Kaper ⋅ October 13, 2023

Thanks Maksym,
I already saw this in the documentation. This will work for things that have records for today, but if the data set has records that stopped 3 months ago, looking at today will show me nothing. What I'm looking for instead is the most recent day, which could be a future date for a projection dataset, which could be 3 months ago for a historical data set that ended 3 months prior, or today. (See https://jsfiddle.net/gnLyjcx8/)
In my case, I have a simulation data set that rolls into the future, and I want to start by filtering by the furthest date in the future (thus my +1 for earliest/latest feature request) 🙂
As always, thank you for the fast reply!
-Bill

Public

Maksym Diachenko ⋅ Flexmonster ⋅ October 16, 2023

Hi, Bill!

Thank you for further explanation of your use case.

For this case, we suggest programmatically setting the filter on the reportcomplete event using the setFilter API call. This solution would require manually retrieving the latest date before setting it into the filter. You are welcome to check the example: https://jsfiddle.net/flexmonster/hkx3oaep/

Looking forward to hearing your feedback.

Best Regards,
Maksym

[Feature proposal] Allow filtering by "Top" or "Bottom" on rows and columns

7 answers

Please login or Register to Submit Answer