I need assistance with a calculated field involving two date parameters: a start date and an end date.
The goal is to filter the data based on these parameters and also to use them in calculated field.
Here’s how it should work:
If the date range is filtered from May 26 to June 25, the calculated field should reflect June’s data.
If the date range is from June 26 to July 25, it should reflect July’s data.
If the range is from July 26 to August 25, it should reflect August’s data.
If the range is from August 26 to September 25, it should reflect September’s data so on
I need help creating this calculated field based on the criteria outlined above.
If the data is filtered using the regular method, it will display month-wise data as per your request.
For example, filtering from May 1 to May 31 will show data for the month of May.
However, if someone filters the data from May 26 to June 25, it will display data for the month of June.
Based on two date parameters you can create a From and To Date range based on calculated fields as follows. I am just creating the values here.
Since the filters can be based on parameters and not calculated fields you need to create another calulated field to produce a boolean (1 or 0) result by checking your datasets date field to be between the two date values from the following calculated fields and filter that calculated field for a value = 1 to get your visuals working fine.
Hello Vaibhav.narwade, Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
As shown in the snapshot, I would like to clarify my request. From January to April, we can follow the 1st to the 30th (or 31st) of each month. However, starting in May, we need to implement the logic indicated above.
I need to incorporate the monthly headcounts using the specified date range logic.
For Headcount I have a separate month-wise field.
We can evaluate both logics for the date range from May to December.
26th may - 25th June = June month
1 June - 30/31 June = June month
This data will be filtered based on the Start date and End date parameters, including headcount. For headcount, I have a month-wise fields.
January headcount,
February headcount and so on…
How do you join your Billing table to Head Count table? I guess what you are trying to do is more to do with the join between the tables than on the visualization
If you could simulate some sample data for the two tables and how you have setup the dataset then I can take a look
Based on your description my original reply with the FromFilterDate and ToFilterDate should work for all your cases. You can give it a try on a smaller dataset and try.
If Giridhar’s suggestion worked for you, please mark it as solution. If you are still having problems, please create a sample analysis in Arena with mock data and include link to it along with clear explanation on the numbers you expect to see for various data filter combinations. This will help the community take a better stab at solving your problem.
