Batch date

huda.h.yazgi · January 11, 2024, 3:17pm

I currently have a Date field in my dataset displaying dates in this format (YYYY-MM-DD) for each row in my dataset. I am creating a summary sheet displaying year-over-year changes utilizing insights. I want the user to have the flexibility to select a date range to filter the summary sheet by. However, in order for the year over year changes insights to display correctly, the user must select 2 years. I want to create a drop-down displaying values:
2016-2017
2017-2018
2018-2019

and when the user selects “2016-2017” I want the entire sheet to filter and show aggregated data for 2016-2017. I believe this can be achieved via parameter and calculated field, I’m just not sure how to set up the calculated field.

DylanM · January 11, 2024, 11:01pm

Hello @huda.h.yazgi, this is definitely possible! Basically, you can create a parameter that is just a string value. Make the default whichever of the options you want it to default to.

Now create your control dropdown and enter all of the values (including the default) that you want the user to be able to select from. Make sure each new value is on a new line.

Now make a calculated field with an ifelse statement:

ifelse(
${dateDropdown} = "2016-2017" AND 
(extract('YYYY', {date}) = 2016 OR extract('YYYY', {date}) =2017, {date},
...remaining options,
NULL
)

Then you can enter this field into your field well rather than your date field. Make sure to add a date filter on this field as well, so you can select the exclude nulls option from the last dropdown in the filter options. You can set a large default date range that will account for all of your data. The relative date filter with the rolling dates option might be the best bet.

I’m going to mark this as the solution, but if you have questions about implementation, please let me know!

huda.h.yazgi · January 12, 2024, 7:55pm

Hi @DylanM! This worked as expected! Thank you so much! The only difference I made was not adding the date filter to exclude nulls. Is that necessary?

DylanM · January 16, 2024, 5:48pm

Hello @huda.h.yazgi, I suppose it depends. If your visual is displaying values how you are expecting, then you should be fine! I normally add it to ensure nulls do not display in my visual.

huda.h.yazgi · February 6, 2024, 5:08pm

Hi @DylanM I noticed an issue when I was testing my dashboard using this. I currently have year over year insights utilizing the batch date calculation you provided me with using the extract date logic. I wanted the drop down control that contains the batch date calculation to also control the table in my dashboard. However, the table only displays a year of data at a time. So, The insights at the top of my dashboard display y-o-y, for example from 2022-2023 an my table displays data for year 2023 only. To achieve that, I added a calculated field to my table to only extract one year of data as opposed to two for my insights. now this works just fine. But when I use the state values in my table it does not filter the insights properly and give me an error message saying, “not enough data”. I suspect because the table only has a year of data as opposed to two in my insights? How do I resolve this problem?
This is how my drop down control looks like:

and it controls the insights and table.

Insights extract 2 years; Calculation used for insight:
ifelse(
${batchdatetest} = “2017” and (extract(‘YYYY’,Date)=2016 or extract(‘YYYY’,Date)=2017),Date,
${batchdatetest} = “2018” and (extract(‘YYYY’,Date)=2017 or extract(‘YYYY’,Date)=2018),Date,
${batchdatetest} = “2019” and (extract(‘YYYY’,Date)=2018 or extract(‘YYYY’,Date)=2019),Date,
${batchdatetest} = “2020” and (extract(‘YYYY’,Date)=2019 or extract(‘YYYY’,Date)=2020),Date,
${batchdatetest} = “2021” and (extract(‘YYYY’,Date)=2020 or extract(‘YYYY’,Date)=2021),Date,
${batchdatetest} = “2022” and (extract(‘YYYY’,Date)=2021 or extract(‘YYYY’,Date)=2022),Date,
${batchdatetest} = “2023” and (extract(‘YYYY’,Date)=2022 or extract(‘YYYY’,Date)=2023),Date,
NULL
)
Table extracts 1 year; Calculation used for table:
ifelse(

${batchdatetest} = “2016” and (extract(‘YYYY’,Date)=2016),Date,

${batchdatetest} = “2017” and (extract(‘YYYY’,Date)=2017),Date,

${batchdatetest} = “2018” and (extract(‘YYYY’,Date)=2018),Date,

${batchdatetest} = “2019” and (extract(‘YYYY’,Date)=2019),Date,

${batchdatetest} = “2020” and (extract(‘YYYY’,Date)=2020),Date,

${batchdatetest} = “2021” and (extract(‘YYYY’,Date)=2021),Date,

${batchdatetest} = “2022” and (extract(‘YYYY’,Date)=2022),Date,

${batchdatetest} = “2023” and (extract(‘YYYY’,Date)=2023),Date,

NULL

)

DylanM · February 6, 2024, 5:45pm

Hello @huda.h.yazgi, what all are you returning in your table visual when you set the filter for that year? Also, what filters are you applying to the table? If you have a 2nd filter on the table related to the date field rather than the filter on the calculated field that excludes nulls you may run into a visual error. Another error may occur if you are utilizing an periodOverPeriod calculations in your table visual that utilize data from a date period that is being filtered out.

huda.h.yazgi · February 6, 2024, 7:11pm

hi @DylanM I am currently using a period over period calculation for my insights and I am using the batch date calculation instead of date. My table contains states as a group by field, the batch date calculation (included above) and the value is a rate. I also have a filter displaying a wide range of dates excluding the nulls as you recommended earlier. I want my users to select a state in the table and use that as a filter for the entire sheet. Could it be that its not working because the insight and table contain two different calculations?

huda.h.yazgi · February 6, 2024, 7:19pm

Or maybe because the table is filtered by the batch date calculated field I created for the table and the insight is not filtered by any date?

DylanM · February 6, 2024, 7:23pm

Hello @huda.h.yazgi, are you able to post an anonymized screenshot of how you are implementing the field and the filter on your table visual? The field should be filtering the data returned, so you shouldn’t need to apply a filter on the date as well. That would likely break the visual and not allow it to display any information.

huda.h.yazgi · February 6, 2024, 7:33pm

This are the fields in my table:

image502×543 27.5 KB
The batch date table calculation is:
ifelse(${batchdatetest} = “2016” and (extract(‘YYYY’,Date)=2016),Date,
${batchdatetest} = “2017” and (extract(‘YYYY’,Date)=2017),Date,
${batchdatetest} = “2018” and (extract(‘YYYY’,Date)=2018),Date,
${batchdatetest} = “2019” and (extract(‘YYYY’,Date)=2019),Date,
${batchdatetest} = “2020” and (extract(‘YYYY’,Date)=2020),Date,
${batchdatetest} = “2021” and (extract(‘YYYY’,Date)=2021),Date,
${batchdatetest} = “2022” and (extract(‘YYYY’,Date)=2022),Date,
${batchdatetest} = “2023” and (extract(‘YYYY’,Date)=2023),Date,
NULL
)
This is what I am using as my filter for the table:
My insight is a period over period insight and it uses this calculation:
ifelse(${batchdatetest} = “2017” and (extract(‘YYYY’,Date)=2016 or extract(‘YYYY’,Date)=2017),Date,
${batchdatetest} = “2018” and (extract(‘YYYY’,Date)=2017 or extract(‘YYYY’,Date)=2018),Date,
${batchdatetest} = “2019” and (extract(‘YYYY’,Date)=2018 or extract(‘YYYY’,Date)=2019),Date,
${batchdatetest} = “2020” and (extract(‘YYYY’,Date)=2019 or extract(‘YYYY’,Date)=2020),Date,
${batchdatetest} = “2021” and (extract(‘YYYY’,Date)=2020 or extract(‘YYYY’,Date)=2021),Date,
${batchdatetest} = “2022” and (extract(‘YYYY’,Date)=2021 or extract(‘YYYY’,Date)=2022),Date,
${batchdatetest} = “2023” and (extract(‘YYYY’,Date)=2022 or extract(‘YYYY’,Date)=2023),Date,
NULL
)
I do not have any filters applied to the insight
When I select a state on my table:

it gives me an error in the insight