
1. Re: how to use max function if expression is already an aggergation
Simon Runc Jul 7, 2017 9:25 AM (in response to joe sparty)hi Joe,
So there are 2 mains ways to perform and aggregate of an aggregate. One is to use LoDs and the other a Table Calculation. I've gone for the Table Calculation...
So I've changed your field to
if WINDOW_MAX([!LoanBalChgDistrict]) <.25 then "Good"
END
and then set it up (btw I'm guessing how you'd use it....so guessed you'd be looking to see if the MAX month for any district is <0.25) like the below
So run the MAX for every month, and restart every District and Region (is the human language version of what it's doing).
I've also brought just the WINDOW_MAX([!LoanBalChgDistrict]) into the tooltip so you can see what it's doing for all districts.
Hope that helps and makes sense, but let me know if not.

2. Re: how to use max function if expression is already an aggergation
Mihai Constantinescu Jul 7, 2017 9:29 AM (in response to joe sparty)You can do a {MAX({FIXED Tooltip : Measurement})}  where Measurement is an AGG

3. Re: how to use max function if expression is already an aggergation
joe sparty Jul 7, 2017 11:17 AM (in response to Simon Runc)Hi Simon,
I looked at the workbook and I think I have a better understanding, but still a bit confused. I'm trying to get the tooltips to match the color legend from month to month. I've made some changes, but still off if you could look I welcome insight on understanding what is wrong.
Cheers,
Joe

4. Re: how to use max function if expression is already an aggergation
Simon Runc Jul 7, 2017 11:45 AM (in response to joe sparty)Hi Joe,
So as you want the ToolTip fields calculated at the same Level of Detail as the Colouring Calculation, I don't think you need an aggregate of an aggregate. This is only if you want the calculation run at a different level of detail than the Viz (such as my example where I was working out the Max of all months for each District)
What I've done is to take the formula that works up the [!LoanBalChgDistrictRisk] colouring field...and then added just the part that's needed for each of the tooltips
Colouring Calc
if ABS([!LoanBalChgDistrict])>=.75 THEN 10
ELSEIF ABS([!LoanBalChgDistrict])<.75 and ABS([!LoanBalChgDistrict])>=.50 THEN 7
ELSEIF ABS([!LoanBalChgDistrict])<.50 and ABS([!LoanBalChgDistrict])>=.25 then 5
ELSEif ABS([!LoanBalChgDistrict])<.25 and ABS([!LoanBalChgDistrict])>=.10 then 3
ELSE 1
END
So ToolTip 7 is
if ABS([!LoanBalChgDistrict])<.75 and ABS([!LoanBalChgDistrict])>=.50 then
STR(ROUND([!LoanBalChgDistrict]*100,0))+'%'+ STR([!LoanBalChgDistrictRisk])
END
and ToolTip 5 is
if ABS([!LoanBalChgDistrict])<.50 and ABS([!LoanBalChgDistrict])>=.25 then
STR(ROUND([!LoanBalChgDistrict]*100,0))+'%'+ STR([!LoanBalChgDistrictRisk])
END
and so on....as you'll see I've also added the [!LoanBalChgDistrictRisk] to the ToolTip (to verify the colouring class each is falling into) and then added this as a String to end of each ToolTip Calculations, so you can see they are doing what you'd expect.
For example this mark...the colouring dim is 7, as is the ToolTip return (we can tell by the 7 at the end)
so it's the Tooltip 7 colour that's wrong (it should be light red/orange)
btw you also don't need the ELSE ""...by having no ELSE part if the IF statement isn't satisfied it just returns NULLs are NULLs don't get plotted!
So I think the calcs are now correct, just a bit of cleaning up of the colours in the ToolTip
Hope that helps

5. Re: how to use max function if expression is already an aggergation
joe sparty Jul 7, 2017 12:06 PM (in response to Simon Runc)Thanks Simion, I understand why I don't need an aggregate of an aggregate.

6. Re: how to use max function if expression is already an aggergation
joe sparty Jul 7, 2017 12:14 PM (in response to Simon Runc)Another question, how can I reference the prior month for the tooltip? I've been trying LOOKUP:
LOOKUP(MONTH([Date]),LAST()1)

7. Re: how to use max function if expression is already an aggergation
joe sparty Jul 7, 2017 12:58 PM (in response to joe sparty)I figured it out, LOOKUP(MIN([Date]),1), but would like to know a trick to convert the number to a Month such as "March".

8. Re: how to use max function if expression is already an aggergation
Simon Runc Jul 7, 2017 1:02 PM (in response to joe sparty)Coolio!
So yes it's the LOOKUP function, but you just need to reference the value you want from the previous month...
So if you wanted the previous months Sales, say it would be
LOOKUP(SUM([Sales]),1)
and then set up the compute using to be Table Across (in your posted example)...and you don't need the LAST() part either. That just returns the LAST thing in the partition, so in your example
LOOKUP(MONTH([Date]),LAST()1)
this would return the last, but one month name for every tooltip.

9. Re: how to use max function if expression is already an aggergation
Simon Runc Jul 7, 2017 1:03 PM (in response to joe sparty)1 of 1 people found this helpfulSo that would be
LOOKUP(MONTH(MIN([Date])),1)