topic Re: Set Analysis with Axes Min/Max, Should Not change on Filter in QlikView

Set Analysis with Axes Min/Max, Should Not change on Filter

a272070 — Mon, 09 Aug 2021 20:47:38 GMT

Hello,

I am using a set analysis expression in my chart.

Sum({1<[Calendar Year] = {"$(=Max({1}[Calendar Year]))"}>} DISTINCT [Sales Percentage]) <-- Used for current year

Sum({1<[Calendar Year] = {"$(=Max({1}[Calendar Year])-1)"} >} DISTINCT [Sales Percentage]) <--- Used for previous year

The dimension for this chart is [Calendar Month].

Here is the chart below that it makes whenever I put the min and max values in the axes tab. I put min(Sales percentage) and max(Sales percentage) for both the current year and previous year expressions in the scale section under the axes tab.

If I don't put the min and max values, then the chart looks like this, which is not as nice and spaced out.

The issue is, I don't want this chart to do anything when I filter, I want it to stay the same as the set analysis should with the {1} where they are currently as shown above...

But the issue is, when I filter and I have the min and max values in the axes tab, it does this:

Is there a way to fix this so it does not change when filtering but I can also use the min and max axes values so that it looks like the second image at all times no matter what filter?

Thanks in advance!

Re: Set Analysis with Axes Min/Max, Should Not change on Filter

stevejoyce — Mon, 09 Aug 2021 21:21:58 GMT

First you need to carry your set analysis to your min/max axis calculation. I am doing >= max year -1 which will include both years.

min({1<[Calendar Year] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage])

max({1<[Calendar Year] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage])

But this (and your original calculation) aren't aggregating Sales percentage by month for min/max... this is just the max row value. That would only be the same if you have 1 value per month.

If you do need to aggregate first it would be like this:

=max({<[Delivery date] = {">=$(=Max({1}[Calendar Year])-1)"}>}aggr(sum({<[Delivery date] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage]), [Calendar Month]))

and change outermost function to min.

Re: Set Analysis with Axes Min/Max, Should Not change on Filter

a272070 — Tue, 10 Aug 2021 19:14:18 GMT

Thank you so much. This worked for me!