Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gthomson4
New Contributor II

Calculated Dimension

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.

Labels (1)
1 Solution

Accepted Solutions
gthomson4
New Contributor II

Re: Calculated Dimension

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)

7 Replies
MVP & Luminary
MVP & Luminary

Re: Calculated Dimension

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
New Contributor II

Re: Calculated Dimension

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
New Contributor II

Re: Calculated Dimension

sorry, that's my 'doh!' moment 🙂
gthomson4
New Contributor II

Re: Calculated Dimension

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.

Capture.JPG

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

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'}>}
Highlighted
gthomson4
New Contributor II

Re: Calculated Dimension

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
New Contributor II

Re: Calculated Dimension

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)