topic Re: Combo chart Scale limitations in QlikView

Combo chart Scale limitations

Kunkulis — Sat, 16 Nov 2024 21:11:00 GMT

Hello,

I have a combo chart that shows YYYY-MM on the X axis and Profit+Loss on the Y axis as bars and as a second Y axis has Number of eateries.
This is a dynamic chart, which changes based on the currency and country selected.
Since i wish for both of the Y axis to be on the same horizontal lines, I needed to create Static Max for them. At first I started with creating 200+ if statement for each of the currency and country, so not really efficient. A kind user gave me this formula

=if(aggr(rank(sum(sales)),Month)<=1,sum(sales))

and it works perfectly.
I used this formula to create roughly 20 if statements in one expression. Part of it looks like this:

=if(aggr(rank(sum(sales)),Month)<=1,if(sum(sales)<10 and sum(sales)>=5,10))

Which should set the Static Max to 10
Unfortunately it is not responding at all to this formula in Static Max. I created a Straight table where I used this expression and it works as it should, returning all the Max values it should as I go through the currencies and countries.

Any idea why is it not working? Because the first variant with the 200+ if statements worked (i didn't get to 200, but in the end it should have been this number. I had 25 of them)

Re: Combo chart Scale limitations

rubenmarin — Mon, 25 Mar 2019 17:31:32 GMT

Hi, I'm not sure of what you are trying for max value...

Your first if checks for rank lower or equal to '1', if it's true it goes to the seconf if. This if is asking for "sum(sales)" between 5 and 10, and if sum(sales) is between this values sets the value to 10. If "sum(sales)" is outside this range it doesn't return any value.

Re: Combo chart Scale limitations

Kunkulis — Tue, 26 Mar 2019 06:39:16 GMT

The first if ranks the graph bars from 1 to , let say, 5 (since there are 5 months), than it uses the first one which is the largest and shows the sum of it, which is checked if it fall in the between 5 and 10, if it is out of this range there is a different bucket - 10 to 20, then 20 to 40, then 40 to 80 etc. until it is way above the max value I have seen in the graph...

Re: Combo chart Scale limitations

rubenmarin — Tue, 26 Mar 2019 08:16:37 GMT

Hi, what I don't know is why the need of bucketing the max value, something like this can do a similar work?:
=if(aggr(rank(sum(sales)),Month)<=1,sum(sales)*1.1)

It adds 10% to higher value.

Re: Combo chart Scale limitations

rubenmarin — Tue, 26 Mar 2019 08:28:33 GMT

An easier "=if(aggr(rank(sum(sales)),Month)<=1,sum(sales)*1.1)" can't do a similar work? it adds 10% to the max value.

Re: Combo chart Scale limitations

Kunkulis — Tue, 26 Mar 2019 08:38:40 GMT

The problem is that this formula is not doing anything, nothing changes as I am using it...

Re: Combo chart Scale limitations

rubenmarin — Tue, 26 Mar 2019 08:56:23 GMT

But in your initial post says "=if(aggr(rank(sum(sales)),Month)<=1,sum(sales)) works perfectly", it really works? and adding the *1.1 doesn't do anything?

Re: Combo chart Scale limitations

Kunkulis — Tue, 26 Mar 2019 10:33:11 GMT

Found a solution that work for me:

=if(max(aggr(sum(sales)),Month)<10 and max(aggr(sum(sales)),Month)>=5 ,10,)

after the last comma, i just have 20 or so more if expressions setting the ranges.