Pivot Table

[Anonymous]

Hi

Following is the screen shot of my application.

It shows the Sales of various city across continents.

There are three dimensions involved here. Continent ,City, Month.

When i select a city say Tokyo i expect the below highlighted Row to display entire Asia's Total.

Also i expect the World total(Sum of three continents) displayed at the last row (Highlighted in the below image)

So using dimensionality and set analysis the implementation is done.

But by problem is when i select Tokyo,

I expect Tokyo's total along with Asia's total(Sum of Tokyo+Sum of Delhi) and

World total(Sum of Asia + Sum of Aus + Sum of Europe) to be shown.

Please follow the below image to understand what i expect.

But the other city's values are also displayed as '-' like,

Could any one help me on this please.

Have attached my sample application.

Please let me know if further information is required on this.

Thanks!

[jonathandienst · Accepted solution]

Is this what you need?

=Pick(Dimensionality() + 1,

  sum({<Continent, City, Month>} Numerator) / Sum({<Continent, City, Month>} Denominator),

  Sum({<City, Month>} Numerator) / Sum({<City, Month>} Denominator),

  Sum({<Month>} Numerator) / Sum({<Month>} Denominator),

  Sum(Numerator) / Sum(Denominator)) * 1000

[Anonymous]

I used the above expression and when i selected tokyo i still get the look as,

I want only Tokyo to be displayed with Continent and Global total. Like,

I dont want to see the ones marked in blue(1st image)

[jonathandienst]

=Pick(Dimensionality() + 1,

  sum({<Continent, City, Month>} Numerator) / Sum({<Continent, City, Month>} Denominator),

  If(Count(City) > 0, Sum({<City, Month>} Numerator) / Sum({<City, Month>} Denominator)),

  Sum({<Month>} Numerator) / Sum({<Month>} Denominator),

  Sum(Numerator) / Sum(Denominator)) * 1000

[Anonymous]

Thanks for the reply.

But still i am able to see the other city's data displayed as '-'

Is there an way to get rid of this?

[jagan]

Hi Koushik,

It is working correctly with the jontydkpi expression for me.  Please find attached file.

Regards,

jagan.

[Anonymous]

Hi Jagan Mohan

Thanks for the reply.

Ya the expression works.

But  i still find  '-' displayed for other city.

In the above screenshot the selected City is 'Tokyo'.

I dont want '-' displayed for other cities.

I expect it to like,

It should only display Tokyo(Because tokyo is selected).

I dont want to see '-'.

Is there an way to implement this?

[jagan]

Hi,

Which version are you using?  I am using Qlikview 11.2 SR5 it is working fine for me.

Regards,

jagan.

[Anonymous]

I am using SR3.

Can you please send me a screenshot of how it looks in SR5 when Tokyo is selected.

[jagan]

Hi,

Check this

Regards,

Jagan.