2 Replies Latest reply on Mar 27, 2013 7:14 AM by Greg Araujo

# calculating min and max values based off a date range

Hi there,

Based on a range of selected dates in my date range quick filter, I am trying to show the first value within the specified date range. In other words...

 report_type report_month net_sales new_net_sales actual 01-Jan-2013 \$100,000.00 \$99,000.00 actual 01-Feb-2013 \$150,000.00 \$147,000.00 actual 01-Mar-2013 \$200,000.00 \$147,000.00 actual 01-Apr-2013 \$225,000.00 \$224,000.00 forecast 01-Jan-2013 \$250,000.00 \$217,500.00 forecast 01-Feb-2013 \$300,000.00 \$247,000.00 forecast 01-Mar-2013 \$350,000.00 \$339,500.00 forecast 01-Apr-2013 \$200,000.00 \$189,000.00

If I select january through march, I would like to display the information as follows...

 actual forecast start_net_sales 100,000 250,000 net_sales 450,000 900,000 new_net_sales 393,000 804,000 end_net_sales 200,000 350,000

i had created a post (which was resolved by Dan Huff - thank you), on calculating the min value...

http://community.tableau.com/message/204854#204854

i thought I could apply the same logic used towards calculating the max value but I am having no luck.

any help is appreciated.

thanks,

greg

• ###### 1. Re: calculating min and max values based off a date range

Greg--

Attached is a new answer. This just requires another calculation to get this to work. In order to get the filter to behave, we essentially just have to do what we did with the window_sum calculations but in a different way.

In this case, I use the lookup function combined with the last function. The lookup function allows us to grab specific values from the table data (the results displayed in the view we get back from the database). Here I use the last() function as the offset so that we put the last value for sum(net sales) into each row in the table. Once this is done with setting the compute using to Pane Down, we can then reapply the filter with a pane down compute using to limit the results.

Hope this helps,

Dan

• ###### 2. Re: calculating min and max values based off a date range

Thanks again for your help Dan.

greg