topic Re: Pivot Table in QlikView

Pivot Table

Anonymous — Fri, 19 Jun 2015 05:25:21 GMT

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!

Re: Pivot Table

jonathandienst — Fri, 19 Jun 2015 05:49:00 GMT

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

Re: Pivot Table

Anonymous — Fri, 19 Jun 2015 05:53:53 GMT

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)

Re: Pivot Table

jonathandienst — Fri, 19 Jun 2015 06:09:07 GMT

=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

Re: Pivot Table

Anonymous — Fri, 19 Jun 2015 06:23:06 GMT

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?

Re: Pivot Table

jagan — Fri, 19 Jun 2015 06:27:15 GMT

Hi Koushik,

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

Regards,

jagan.

Re: Pivot Table

Anonymous — Fri, 19 Jun 2015 06:36:08 GMT

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?

Re: Pivot Table

jagan — Fri, 19 Jun 2015 06:37:16 GMT

Hi,

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

Regards,

jagan.

Re: Pivot Table

Anonymous — Fri, 19 Jun 2015 06:46:30 GMT

I am using SR3.

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

Re: Pivot Table

jagan — Fri, 19 Jun 2015 07:26:54 GMT

Hi,

Check this

Regards,

Jagan.

Re: Pivot Table

Anonymous — Fri, 19 Jun 2015 07:30:45 GMT

I am not getting it

is it because of version?

is there an alternate way where we can achieve the same in SR3?

Could you please help me on this?

Re: Pivot Table

jagan — Fri, 19 Jun 2015 07:34:09 GMT

Not Sure. Did you selected supress Null values and Supress zero values option in presentation tab.

Regards,

Jagan.

Re: Pivot Table

Fri, 19 Jun 2015 07:54:36 GMT

This is your original application screenshot. with our dimensionality formulas .

Your original applications works alright .

Version 11 SR9

Re: Pivot Table

Anonymous — Mon, 22 Jun 2015 04:30:27 GMT

Thanks for the answer

Re: Pivot Table

Anonymous — Mon, 22 Jun 2015 04:32:01 GMT

Hi Jagan Mohan

Thanks a lot for your help.

I am using SR6 now and it is working as expected.

Thanks