topic Results For Lowest Level Dimension in QlikView

Results For Lowest Level Dimension

Anonymous — Mon, 11 May 2015 20:35:26 GMT

Have a Data in this form:

Region	Country	City	Sales in millions	Quarter
North America	US	New York	434.5881266	Q1
North America	US	New York	593.5616063	Q2
North America	US	New York	203.5437846	Q3
North America	US	New York	212.2659167	Q4
North America	US	Canada	185.3205976	Q1
North America	US	Canada	592.3597726	Q2
North America	US	Canada	847.6435894	Q3
North America	US	Canada	70.37217548	Q4
Europe	UK	London	422.3300851	Q1
Europe	UK	London	773.7030397	Q2
Europe	UK	London	485.2654965	Q3
Europe	UK	London	583.7337934	Q4
Asia	China	Hong Kong	2.395353774	Q1
Asia	China	Hong Kong	572.2251346	Q2
Asia	China	Hong Kong	18.25943108	Q3
Asia	China	Hong Kong	146.5491327	Q4

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

City	Sum of Sales
New York	4812.207051
Canada	4812.207051
London	1900.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.

Re: Results For Lowest Level Dimension

sunny_talwar — Mon, 11 May 2015 20:44:49 GMT

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

sunny_talwar — Mon, 11 May 2015 20:49:42 GMT

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)

Re: Results For Lowest Level Dimension

maxgro — Mon, 11 May 2015 21:38:32 GMT

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

this expression should work

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

Re: Results For Lowest Level Dimension

anbu1984 — Tue, 12 May 2015 05:40:38 GMT

Check this app

Re: Results For Lowest Level Dimension

Anonymous — Tue, 12 May 2015 13:53:34 GMT

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

sunny_talwar — Tue, 12 May 2015 13:55:13 GMT

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