Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

arsalanam
Contributor

Results For Lowest Level Dimension

Have a Data in this form:

    

RegionCountryCitySales in millionsQuarter
North AmericaUSNew York434.5881266Q1
North AmericaUSNew York593.5616063Q2
North AmericaUSNew York203.5437846Q3
North AmericaUSNew York212.2659167Q4
North AmericaUSCanada185.3205976Q1
North AmericaUSCanada592.3597726Q2
North AmericaUSCanada847.6435894Q3
North AmericaUSCanada70.37217548Q4
EuropeUKLondon422.3300851Q1
EuropeUKLondon773.7030397Q2
EuropeUKLondon485.2654965Q3
EuropeUKLondon583.7337934Q4
AsiaChinaHong Kong2.395353774Q1
AsiaChinaHong Kong572.2251346Q2
AsiaChinaHong Kong18.25943108Q3
AsiaChinaHong Kong146.5491327Q4

Need to create a straight chart with lowest level Dimension (City) and show Sum of Sales for the Region like this:

  

CitySum of Sales
New York4812.207051
Canada4812.207051
London1900.36539
Hong Kong

2139.817331

But tried writing expression like:

=Sum({1} TOTAL <Region> [Sales in millions])

gives grand total in all rows

Tried:

=Aggr(Sum({<Country,City>} [Sales in millions]), Region)

But shows only one row for North America i.e. Honk Kong.

1 Solution

Accepted Solutions

Re: Results For Lowest Level Dimension

Actually that did not work, try this instead:

=Aggr(Sum({1} TOTAL <Country> [Sales in millions]), City , Country) or

=Aggr(Sum({1} TOTAL <Region> [Sales in millions]), City , Country, Region)

View solution in original post

6 Replies

Re: Results For Lowest Level Dimension

Try this:

=Sum({1} TOTAL <Country> [Sales in millions])



you don't really need 1 for this calculation to come through. Use one if this expression should not change results based on any selections.

HTH

Best,

Sunny

Re: Results For Lowest Level Dimension

Actually that did not work, try this instead:

=Aggr(Sum({1} TOTAL <Country> [Sales in millions]), City , Country) or

=Aggr(Sum({1} TOTAL <Region> [Sales in millions]), City , Country, Region)

View solution in original post

MVP
MVP

Re: Results For Lowest Level Dimension

if you add the Region dimension and then hide Region in Presentation tab

this expression should work

=Sum({$} TOTAL <Region> [Sales in millions])

Highlighted
anbu1984
Honored Contributor III

Re: Results For Lowest Level Dimension

Check this app

arsalanam
Contributor

Re: Results For Lowest Level Dimension

Thank you sunindia, Massimo and anbu, they all serve the purpose. Since, I need to mark one as correct, I'd do it for sunindia since that came in first.

Re: Results For Lowest Level Dimension

There is always 'Mark as Helpful' options to reward others for their contributions