Calculated Dimension

[gthomson4]

Hi,

I'm trying to create a chart that has -

1. Top 50% of countries
2. + Australia

The following works when there is no selection

=Aggr( DISTINCT
  	If( RangeSum( Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo() ))
               	      / Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity) < 0.5
	OR Country='Australia'
      ,Country )
//    sorted structure parameter
      ,(Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity),Desc))
    )

but when I select 'Europe' I only get Russia & Germany which is correct for the 50% part, but can't add Australia. (i.e. I want Australia regardless of where it comes in the rankings) I tried using a reference line but that didn't work either.

Any ideas would be appreciated.

[marcus_sommer]

Maybe this is helpful:

=Aggr( DISTINCT
        If( RangeSum( Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo() ))
                     / Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity) < 0.5
        OR Country='Australia'
      ,only({< Country  = >} Country) )
//    sorted structure parameter
      ,(Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity),Desc))
    )

- Marcus

[gthomson4]

thanks,

but doh! - just add 'Australia' to the end of the IF statement. 

Now I've got my dimensions but the Australia values are all wrong though Russia & Germany are ok?

Sum({$<RecType={2}, CountryFlag={1}, Country=p(Country)+{'Australia'}, UoM={'MT'}, CalYear>} Quantity)

[gthomson4]

sorry, that's my 'doh!' moment 🙂

[gthomson4]

ok, I've made progress on this but still have a small issue.

To get the top 50% and Australia I used this:

=Aggr( DISTINCT
  	If( RangeSum( Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo() ))
               	      / Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity) < 0.5
      ,(Country) ) 
//    sorted structure parameter
      ,(Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity),Desc))
    )
&Aggr( Only({1<Country={'Australia'}>} (Country) ), Country)

Basically appending another aggregation result where 'Only' returns just Australia (thanks Marcus) 

This works except that when I filter on 'Oceania' I get a dimension labelled "AustraliaAustralia" though the figures are correct. i.e. it is being selected in both Aggr() 1 & 2.

I thought I could use {1-$} in the set analysis to exclude Australia from the 2nd part if it was already in the 1st part, however this results in every country in the region being listed, not Australia, and "RussiaRussia" & "GermanyGermany" dimensions. It's as though it is doing the exact opposite of what it should!?

e.g. &Aggr( Only({1-$<Country={'Australia'}>} (Country) ), Country)

Any ideas would be appreciated.

[sunny_talwar]

Try this

=If(
Aggr(RangeSum(Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo()))/Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity), (Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), Desc))) < 0.5
  or
Country = 'Australia',
Country)

and this set analysis in your expression

{<Country += {'Austrlia'}>}

[gthomson4]

Thanks Sunny, however the Aggr() seems to ignore Set Analysis. The following doesn't pick up Australia:

=Aggr({1<Country += {'Australia'}>} DISTINCT
  	If( RangeSum( Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo() ))
               	      / Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity) < 0.5
	Or Country='Australia'
     ,Country  )	
//    sorted structure parameter
      ,(Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity),Desc))
    )
//&Aggr( Only({1<Country={'Australia'}>} (Country) ), Country)

I also tried using Only() as the condition but no difference. (and various other combinations)

It almost works when I append the (commented)  Aggr() using Only() but I get 'AustraliaAustralia' when selecting 'Oceana', i.e. it picks it up in both.

Interestingly, when I use {1-$< in the 2nd Aggr() I get all the remaining countries of the 1st Aggr() but not 'Australia'. It is as though it is doing the exact opposite of what I expect which is the 2nd Aggr should include everything not in the 1st?

[gthomson4 · Accepted solution]

I have a solution:

=Aggr( DISTINCT
//	get the top 50% of countries in the current selection     
  	If( (RangeSum( Above(Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity), 1, RowNo() ))
               	       / Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} TOTAL Quantity)			) < 0.5
      ,Country ) 
//    sorted structure parameter
      ,(Country,(=Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}>} Quantity),Desc))
    )
//	append Australia if it isn't part of the current selection
&Aggr ( DISTINCT 
	If(GetSelectedCount(UNContinentalRegion) or GetSelectedCount(UNSatisticalRegion)
      ,Only({1<Country={'Australia'},UNContinentalRegion=,UNSatisticalRegion=>-$<Country=P(Country)>} Country)
      ,Only({$<Country={'Australia'} >} Country)    )
    , Country )
 
/*

It now works unless Australia is part of the 1st selection but not in the top 50%, e.g. Asia + Oceania. I avoid duplicating Australia by excluding the 1st selection from the 2nd. Where there is no selection of Region or Sub-region then it just adds Australia. 

I had to match the set analysis in the measure like this:

Sum({$<RecType={2}, CountryFlag={1}, UoM={'MT'}, CalYear>
	+$<RecType={2}, CountryFlag={1}, UoM={'MT'}, CalYear, Country={'Australia'},UNContinentalRegion=,UNSatisticalRegion=>}
	Quantity
    )

/*

So, this is the required set + specifically Australia disregarding Region & Sub-region. I used a union in this way to avoid including other countries. 

I don't imagine this is optimal but it works. Thanks for you suggestions.

(And yes 'Statistical' is miss-spelt)