I have a table which in which I have data along with date. I want to collectively show data with respect to month instead of date and then apply filter to show only between selected months.
For e.g.
I have a table
Cost Date
150 01/02/2023
200 02/02/2023
500 05/03/2023
750 15/03/2023
350 10/04/2023
1000 20/05/2023
I want to convert this table to:-
Cost Date
350 02/2023
1250 03/2023
350 04/2023
1000 05/2023
And then I want to apply filter on month, like start month - 02/2023, end month - 04/2023, then -
@Harshvardhan do you want to convert data from Data to Month? or only some data to month but some of them still keeping date?
if I refer to the above example, only Feb and Mar of 2023 will be converted to month but Apr and May still keeping date format in the same column?
You can use the date table and handle the month aggregation in the table.
in below 2 tables, I am using the same data set and using order date as data source
I can aggregate the table by month according to below screenshot
You may add a filter. By default, it shows as date filter but you can update the format according to below screens
@royyung I believe in this approach, I have to select date from date picker, the format will change but date will also be selected. Value of Cost will change with respect to date, for example if I choose start month - 01/02/2023 and end month - 09/04/2023, in filter these selections of date will be shown as YYYY/MM i.e start month - 02/2023 and end month - 04/2023, but in output table 4th month will not be shown as our data has value after 09/04/2023.
Cost Date
350 02/2023
1250 03/2023
Am I missing something or this is the expected result from this filter.
Is there anyway to have same value in whole month, irrespective of the date that I select?
@Harshvardhan yes, you are right. sorry about that
Month filter is one of the feature request but not natively supported yet
You may consider below 2 options which are not exactly what you are asking but hope that they can give you some insights
Create a calculated field and make the month as String. For instance OrderMonth:substring(toString({Order Date}),1,7)
And create a list box of the month
The first option do not provide a date/month range filter. In this second option, you may use a date range filter and allow user to select the data aggregation level(day/month/year)
