Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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...
The problem is that this formula is not doing anything, nothing changes as I am using it...
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.