Date Range Filter (Quarterly)

[KathB27]

Hi All,

i have a column “Shipment Date” and “Status’. I want to create a chart of the status and i also want to create a filter showing shipments quarterly.  Almost the same as below sample app:

https://www.appsheet.com/Template/AppDef?appName=Interactivedashboardfilter-71626&utm_source=share_a...

i just do not know what will be the expression to filter the date by quarter.

Any help will be truly truly appreciated. 
Thanks All!

[WillowMobileSys · Accepted solution]

Since Quarters are defined by their start/end dates, you will need to apply that into the Slice filter criteria.

Let's say you have filter buttons Q1, Q2, Q3 and Q4 and when tapped fill a Filter table column named Quarter.

Using the same approach as in the example app, you would create a filter in the Slice like this:

IFS(
     ANY(Filter[Quarter]) = "Q1",  
           AND(DATE("01/01/2022") <= [Shipment Date], 
               [Shipment Date] <= DATE("03/31/2022")
           ),
     ANY(Filter[Quarter]) = "Q2",  
           AND(DATE("04/01/2022") <= [Shipment Date], 
               [Shipment Date] <= DATE("06/30/2022")
           ),
...
<<repeat for Q3 and Q4>>
...
)

 I hope this helps!

[KathB27 · Accepted solution]

Thank you very much @WillowMobileSys 

I am actually just using 1 Table: BUILDPLAN

The (Quarter Column) i created as Enum but i already prefilled it in my data source.

I want to create a filter slice and show all the Q3, Q4 whichever the user chooses and it will also flow to the chart

In the UX tab, it currently looks like this:

I created a REF Chart View of the Status

I put in the REF Filter View as well

however whenever i choose any of the quarters the chart does not follow :(((

This is a sample of my Buildplan table

Hope you can help with this too

Thanks a lot!!!

[WillowMobileSys · Accepted solution]

Since Quarters are defined by their start/end dates, you will need to apply that into the Slice filter criteria.

Let's say you have filter buttons Q1, Q2, Q3 and Q4 and when tapped fill a Filter table column named Quarter.

Using the same approach as in the example app, you would create a filter in the Slice like this:

IFS(
     ANY(Filter[Quarter]) = "Q1",  
           AND(DATE("01/01/2022") <= [Shipment Date], 
               [Shipment Date] <= DATE("03/31/2022")
           ),
     ANY(Filter[Quarter]) = "Q2",  
           AND(DATE("04/01/2022") <= [Shipment Date], 
               [Shipment Date] <= DATE("06/30/2022")
           ),
...
<<repeat for Q3 and Q4>>
...
)

 I hope this helps!

[KathB27 · Accepted solution]

Thank you very much @WillowMobileSys 

I am actually just using 1 Table: BUILDPLAN

The (Quarter Column) i created as Enum but i already prefilled it in my data source.

I want to create a filter slice and show all the Q3, Q4 whichever the user chooses and it will also flow to the chart

In the UX tab, it currently looks like this:

I created a REF Chart View of the Status

I put in the REF Filter View as well

however whenever i choose any of the quarters the chart does not follow :(((

This is a sample of my Buildplan table

Hope you can help with this too

Thanks a lot!!!

[Former Community Member · Accepted solution]

For this approach to work, it requires 2 tables, a Slice and two Views:

1) A Filter table to specify HOW to filter the data rows.  This could be just a single column single row table.

2)  A data table to be filtered.

3)  A Slice that applies the Filter input to the data table to get the filtered rows.

4) The Filter view set to show the single table row.

5)  The data View (in your case a Chart) that uses the Slice to determine the rows to display.

The idea is this...when you change the Filter in the Filter view, the Filter table row is updated.  The Slice will automatically adjust based on the new selection.  With the data view using the Slice as the datasource, it too will automatically adjust when the filtered rows change. 

Follow the example app, plugging in your bits where different and you'll get exactly the results you want.