9 Replies Latest reply on Mar 27, 2020 10:20 AM by Jim Dehner

# How to find the Min and Max of an aggregate in view only

Part of one of the Viz's I'm trying to make is for a summary page that just shows a quick tick trend line for the last 3 points (daily, weekly, etc).

That works fine, what I'm now trying to get is the min and max of those values.

I've tried something like this, but it's not just using the points in view, it's pulling from the whole data set

{max({ fixed [Date Granularity Calc ]:SUM([Gross_FTE])})}

Is there a way to only have it look at the data in the view?

The next, more complicated part is that I have 3 separate data sets and I'd like to be able to pull back the Min and Max across all of them, but I'd like to solve the first bit first

thanks as always

• ###### 1. Re: How to find the Min and Max of an aggregate in view only

hi Simon

the expression { Max(calculation)}    says to take the max from the entire data set   - it si equivalent to { fixed: Max(calculation)}

if you want to limit the calculation to what is in the view you would have to include the dimensions in the lod   { fixed dimension : Max(calculation)}

hard to say much more without seeing your book

Jim

• ###### 2. Re: How to find the Min and Max of an aggregate in view only

Hi Simon,

It is hard to guess without looking at your Viz.  You need to add partition to our outer LOD on how you want  your resulted to be segmented by.  It will follow a format like this...

{FIXED [dim1],[dim2]:max({ fixed [Date Granularity Calc ]:SUM([Gross_FTE])})}

• ###### 3. Re: How to find the Min and Max of an aggregate in view only

Hi Jim

I'd tried that, but as the Calculation in question needs to be a SUM, it won't work.

Unfortunately I can't upload the workbook as my company doesn't allow it

thanks

• ###### 4. Re: How to find the Min and Max of an aggregate in view only

well then the other option is to use table calculations -

of make up a book with dummy data tahat has the same issue and post it here

Jim

• ###### 5. Re: How to find the Min and Max of an aggregate in view only

Hi

I've recreated the basic view here  -Tableau Public

So What I need is to have the MAX FTE be the max that is shown on the screen

added labels to point value and my version of the MAX calc

Also, not sure why, but when selecting "week" the view disappears, it only does it on this version, on my "real" version it is fine. (Using 2019.2 for work and 2020.1 for this view)

Thanks all

• ###### 6. Re: How to find the Min and Max of an aggregate in view only

see the attached

You have a table calculation filter (last()<3)    that is the issue

The filter affects the view but not the table that underlys the worksheet

- the analogy is like placing a window over the table - so it changes what you see but not the rest of the table that can't be seen

so I rewrote the lod to look like this

and then used it in  this to return the max value in the filtered view -

i returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 7. Re: How to find the Min and Max of an aggregate in view only

Simon -

Re-write your MAX FTE formula to the following, since you put a filter to only show the last 3 in your Viz.  (Model attached).  The devil sometimes is in the details

WINDOW_MAX(SUM([Gross FTE]),0,LAST())

• ###### 8. Re: How to find the Min and Max of an aggregate in view only

Hi both Jim and Budi

thanks for this, they both work, but only for the daily granularity, once you select another view it seems to stop working.

many thanks

• ###### 9. Re: How to find the Min and Max of an aggregate in view only

OK Simon see the attached - this is sort of a brute force way (BYW week is not working at all - did not look at it)

and this

will get a max of the last 3

Jim