7 Replies Latest reply on Oct 10, 2018 8:04 PM by Deepak Rai

# How to filter only Minimum and Maximum in a chart for a date column

Hi All,

I made a bar chart which typically shows last 3 months data. (Bar for every week) I want to apply a filter in my worksheet, that typically shows only Minimum and Maximum of the date field. For example, currently it has data from July 8 to October 8, and bar chart for every week. I want to show only 2 bar charts, one for maximum date (October 8) and one for minimum date(July 8). This max and min filter should keep changing based on current data's max and min values of date field.

how do I achieve this? Appreciate any suggestions.

• ###### 1. Re: How to filter only Minimum and Maximum in a chart for a date column

Hi Vignesh,

You forgot to attach workbook, so just showing you a way to do it, which otherwise I could have created myself with your attached Workbook.

Now, Just Do this, Create your View with dates  on column and Sales on Rows

Create this Calculation

Your Date={MIN(your Date)} and Your Date={MAX(your Date)}

Drag it to Filter and Select TRUE.

Thanks

Deepak

• ###### 2. Re: How to filter only Minimum and Maximum in a chart for a date column

Hi Vignesh,

Here it is.

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 3. Re: How to filter only Minimum and Maximum in a chart for a date column

Thank you, that worked.

• ###### 4. Re: How to filter only Minimum and Maximum in a chart for a date column

Hi Deepak, Just one more query. Can you help me with calculated field, If i want to have two bars, one with Max date and one with 3 months before the max date. For example, One bar with max date as 10/7/2018 , and one with 3 months from that date. How do we achieve this?

thanks

• ###### 5. Re: How to filter only Minimum and Maximum in a chart for a date column

DATETRUNC('day'. Sampletime)={MAX(DATETRUNC('day'. Sampletime))} OR DATETRUNC('day'. Sampletime)=DATEADD('month',-3,{MAX(DATETRUNC('day'. Sampletime))})

If it Does not work, I would provide solution later.

• ###### 6. Re: How to filter only Minimum and Maximum in a chart for a date column

Hi Deepak,

The second condition to get 3 month older doesn't seem to work. Please provide with an alternative when you get time. Thanks in advance.

• ###### 7. Re: How to filter only Minimum and Maximum in a chart for a date column

You have 3 months data total  and which was confusing. Anyway, Since you want to see things at Month Level so I used -2 in the Calc, You can use -3 in your Original Data set.

Hope this is Helpful

Deepak