topic Re: Formating pivot chart in QlikView

Formating pivot chart

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

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).

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't

For the moment i get the following chart :

Thanks

Re: Formating pivot chart

chrismarlow — Mon, 26 Nov 2018 20:07:05 GMT

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

sunny_talwar — Mon, 26 Nov 2018 20:08:21 GMT

Something like this?

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>')

Re: Formating pivot chart

m_woolf — Mon, 26 Nov 2018 20:08:32 GMT

https://community.qlik.com/t5/QlikView-Documents/How-to-use-Dimensionality/ta-p/1485559

Re: Formating pivot chart

sergio0592 — Mon, 26 Nov 2018 20:33:23 GMT

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

Re: Formating pivot chart

manoranjan_321988 — Tue, 27 Nov 2018 14:12:33 GMT

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

Re: Formating pivot chart

sunny_talwar — Tue, 27 Nov 2018 14:15:35 GMT

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

Re: Formating pivot chart

manoranjan_321988 — Tue, 27 Nov 2018 14:33:01 GMT

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

sunny_talwar — Tue, 27 Nov 2018 14:36:10 GMT

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 🙂