Struggling to get to grips with DateRange

App Building
,
1

So I am building an internal tool which displays a funnel.
In the funnel I want to give users the ability to select certain months on a year to see the results.
I pull data from a MySQL database and Google spreadsheets and I am not very well versed in Javascript, I am pretty handy in SQL.

Currently I have this code in the Transform tool:
const startDate = new Date({{dateRange1.value.start}});
const endDate = new Date({{dateRange1.value.end}});

const filteredData ={{ newUsers.data}}.filter(row => {
const rowDate = new Date(row.date);
return rowDate >= startDate && rowDate <= endDate;
});

return formatDataAsObject(filteredData);

But it is a mess, sometimes it works, other times it doesnt show the first month but does show all other months and today it just completely stopped.
Is this the only way to have a datefilter applied over multiple tables (I just re-use this code) or it there this super easy way that I am not seeing atm?

2

Hello @Marjin_Q,

If you wanted select just by month, the reason why sometimes it works and sometimes it fails is because you have to choose a start and end date. If the full month was not chosen, the transformer would return partial data for the month. Here is the code for the transformer, maybe it would fix it. You can also change the dateRange component to just a date component and update the code in transformer, since endDate isn't needed.

const selectedMonth = new Date({{dateRange1.value.start}}).getMonth();
const selectedYear = new Date({{dateRange1.value.start}}).getYear();

const filteredData ={{ newUsers.data}}.filter(row => {
const rowMonth = new Date(row.date).getMonth();
const rowYear = new Date(row.date).getYear();
return (selectedMonth === rowMonth && selectedYear === rowYear);
});

return formatDataAsObject(filteredData);
3

Hey!
Thanks for your response.
I just tried your code and no luck...
I get the error "__var2.filter is not a function"

I have selected 01/02/2023 - 31/05/2023 and all 3 tables give different date ranges.
One shows all available dates, one only shows feb and the other one shows nothing.

4

Hey @Marijn_Q!

Would you mind sharing more about what {{ newUsers.data }} looks like?

The fact that you're seeing __var2.filter is not a function suggests that it's not returning an array at the moment you try and run the transformer, so hovering over the value might help give a sense of what's going on:

It might also be helpful to see how the date column is formatted in your query. If you're using DD/MM/YYYY format it can cause issues with the Date constructor:

The moment library that comes built-in with Retool is particularly helpful for that because you can specify the format of the string you're passing: