topic Re: [Bar Chart] Aggr Text on Axis in QlikView

[Bar Chart] Aggr Text on Axis

Tue, 24 May 2016 19:09:22 GMT

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:

Country	Product	Value
Argentina	Children's Clothes	51
Argentina	Men's Clothes	250
Argentina	Women's Clothes	86
Belgium	Children's Clothes
Belgium	Men's Clothes	36
Belgium	Women's Clothes	190
Canada	Children's Clothes
Canada	Men's Clothes
Canada	Women'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:

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

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.

Re: [Bar Chart] Aggr Text on Axis

sunny_talwar — Tue, 24 May 2016 19:26:00 GMT

How about this:

If([Percent] > 0,

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

Re: [Bar Chart] Aggr Text on Axis

Tue, 24 May 2016 21:23:27 GMT

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

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

All credits go to them juan.navarro84 and alejandro_fa_83‌

Thanks Guys ✌️

Re: [Bar Chart] Aggr Text on Axis

sunny_talwar — Tue, 24 May 2016 23:37:25 GMT

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

vs. this when I open yours

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

Re: [Bar Chart] Aggr Text on Axis

swuehl — Tue, 24 May 2016 23:58:28 GMT

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)

Re: [Bar Chart] Aggr Text on Axis

Wed, 25 May 2016 14:08:14 GMT

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!

Re: [Bar Chart] Aggr Text on Axis

Wed, 25 May 2016 14:19:00 GMT

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.

Re: [Bar Chart] Aggr Text on Axis

EGilbert — Tue, 18 Feb 2020 17:26:44 GMT

Can I ask how you got the total count on the axis to be listed once and not once for each product? I do something very similar but my text on axis totals list for each of the secondary dimensions in each bar?