Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

[Bar Chart] Aggr Text on Axis

Hi everyone!

I have a bar chart showing the percentage of products by country, where I set two dimensions and expressions. One of the expressions is the total of products aggregated by country and needs to be as "text on axis".


The data is something similar to this:

CountryProductValue
ArgentinaChildren's Clothes51
ArgentinaMen's Clothes250
ArgentinaWomen's Clothes86
BelgiumChildren's Clothes
BelgiumMen's Clothes36
BelgiumWomen's Clothes190
CanadaChildren's Clothes
CanadaMen's Clothes
CanadaWomen's Clothes

Full data within the attachments

As you can see some countries has missing value for one or more Products.

The chart has Country and Product as dimensions (Stacked) and the two expressions are:

Exp1 : SUM(Value) / SUM(TOTAL <Country> Value)

Exp2 [Text on axis]: AGGR( SUM(Value), Country )

With the full data set I got this as a result:

Object1_highlighted_small.jpg

The numbers highlighted in red have the missing symbol "-" and looks like strikethrough for the last digit.

I tried to change the "Text on Axis" expression to this:

IF(ISNULL(AGGR( SUM(Value), Country )),'',AGGR( SUM(Value), Country ))

which eliminates the missing symbol, but shows the countries without values

Object2_highlighted_small.jpg

I tried to supress that country but nothing seems to work. I don't want to use a calculated dimension and I can not eliminate the records without value because the data is dummy and the real data has more information.

Does anybody have an idea to how to deal with this?

All the test files are in the attachments.

1 Solution

Accepted Solutions
Not applicable

Re: [Bar Chart] Aggr Text on Axis

That still give me the country without values (Canada in this example) and some others still strikethrough.

Object3_highlighted_small.jpg

With the help of my buddies in the office this problem was solved by modifying the expression to:

SUM(TOTAL <Country> AGGR(SUM(Value),Country))

Object4_small.jpg

All credits go to them juan.navarro84 and alejandro_fa_83

Thanks Guys ✌️

View solution in original post

6 Replies

Re: [Bar Chart] Aggr Text on Axis

How about this:

If([Percent] > 0,

If(RowNo() = 1, AGGR(SUM(Value), Country), ' '))

Not applicable

Re: [Bar Chart] Aggr Text on Axis

That still give me the country without values (Canada in this example) and some others still strikethrough.

Object3_highlighted_small.jpg

With the help of my buddies in the office this problem was solved by modifying the expression to:

SUM(TOTAL <Country> AGGR(SUM(Value),Country))

Object4_small.jpg

All credits go to them juan.navarro84 and alejandro_fa_83

Thanks Guys ✌️

View solution in original post

Re: [Bar Chart] Aggr Text on Axis

Not sure why it might have not worked for you, but this is what I see when I open the application

Capture.PNG

vs. this when I open yours

Capture.PNG

If this worked for you,

SUM(TOTAL <Country> AGGR(SUM(Value),Country))

then you can also try this:

AGGR(NODISTINCT SUM(Value),Country)

Best,

Sunny

MVP
MVP

Re: [Bar Chart] Aggr Text on Axis

Why do you need the advanced aggregation here?

This worked for me and I would be surprised if it's not working at any site

=SUM(TOTAL <Country> Value)

Not applicable

Re: [Bar Chart] Aggr Text on Axis

I think is all about the QlikView version. I'm currently using v11.20 SR13 and I notice some differences vs previous builds (also with v12).

If you take a look at the first image you post, the Belgium value is moved a little bit down. I enable the fast change option in the chart so you can se what is happening in the straight table format.

The last expression that you suggest also works for me. Thanks!

Not applicable

Re: [Bar Chart] Aggr Text on Axis

You're absolutely right. That Also worked and I think is best approach. I will perform further test with the real data and see how it handles. Thank you for your comments.