Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

sergio0592
Valued Contributor II

Formating pivot chart

Hi all,

I have  two dimensions (country and city) and one expression (sales). What i'am trying to,achieve is the output below (made on Excel).

P1.jpg

l can't remember if it's possible with Qlikview. I think it could be esay if we can hide dimension in pivot chart. But we can'tSmiley Sad

For the moment i get the following chart :

P2.jpg

Thanks

1 Solution

Accepted Solutions

Re: Formating pivot chart

Something like this?

image.png

To do this, I created an island table like this in the script

Table:
LOAD * INLINE [
    Country, City, Sales
    France, Nice, 789
    France, Toulouse, 345
    Italy, Milano, 799
    Italy, Roma, 490
    Italy, Turino, 634
    UK, Liverpool, 457
    UK, London, 678
    UK, Manchester, 450
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

Now create a straight table like this

Dimension

=Pick(Dim, City, 'Total ' & Country)

Expression

Sum(Sales)

and sort the dimension using this

(Rank(Country)) + Dim/10

Use this for background color of expression and dimension

=If(Dim = 2, Yellow())

and use this for text formatting

=If(Dim = 2,'<b>')

View solution in original post

8 Replies
chrismarlow
Valued Contributor

Re: Formating pivot chart

Hi,

You can get close checking 'Indent Mode' on Style tab & changing the Country dimension to a calculated dimension 'Total ' & Country.

Interested to see if any better suggestions.

Cheers,

Chris.

Re: Formating pivot chart

Something like this?

image.png

To do this, I created an island table like this in the script

Table:
LOAD * INLINE [
    Country, City, Sales
    France, Nice, 789
    France, Toulouse, 345
    Italy, Milano, 799
    Italy, Roma, 490
    Italy, Turino, 634
    UK, Liverpool, 457
    UK, London, 678
    UK, Manchester, 450
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

Now create a straight table like this

Dimension

=Pick(Dim, City, 'Total ' & Country)

Expression

Sum(Sales)

and sort the dimension using this

(Rank(Country)) + Dim/10

Use this for background color of expression and dimension

=If(Dim = 2, Yellow())

and use this for text formatting

=If(Dim = 2,'<b>')

View solution in original post

mwoolf
Honored Contributor II

Re: Formating pivot chart

sergio0592
Valued Contributor II

Re: Formating pivot chart

It works, great Sunny as usual Smiley Happy. Even if i can't understand how the link is made between contry and value 2 of the dim table.

manoranjan_321988
New Contributor

Re: Formating pivot chart

can u pls explain the (Rank(Country)) + Dim/10 

Re: Formating pivot chart

Add the above as an expression and you will understand what i was trying to do )
manoranjan_321988
New Contributor

Re: Formating pivot chart

i agree that it will make sort, but i can't understand,

 

rank(country) will be 1 2 3

 

but why adding with DIM/10 can explain deeply pls

example 

Re: Formating pivot chart

I have no problem in doing so, but if I do, then you will not have a chance to learn on how to break expressions and understand what they are doing.

Hint: Break the expression and see what it is doing
1) (Rank(Country)) + Dim/10
2) (Rank(Country))
3) Dim/10

and see there values 🙂