topic Re: Calculated Dimension in App Development

Calculated Dimension

gthomson4 — Sat, 16 Nov 2024 05:45:51 GMT

Hi,

I'm trying to create a chart that has -

Top 50% of countries
+ 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.

Re: Calculated Dimension

marcus_sommer — Thu, 23 May 2019 09:22:02 GMT

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

Re: Calculated Dimension

gthomson4 — Thu, 23 May 2019 09:46:13 GMT

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)

Re: Calculated Dimension

gthomson4 — Thu, 23 May 2019 09:53:11 GMT

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

Re: Calculated Dimension

gthomson4 — Fri, 24 May 2019 10:07:36 GMT

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.

Re: Calculated Dimension

sunny_talwar — Fri, 24 May 2019 11:52:19 GMT

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'}>}

Re: Calculated Dimension

gthomson4 — Tue, 28 May 2019 09:19:49 GMT

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?

Re: Calculated Dimension

gthomson4 — Thu, 06 Jun 2019 01:59:06 GMT

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)