topic Re: Set analysis expression as dynamic max in line graph?!? in QlikView

Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 10:03:19 GMT

Hi all,

In my line graph I plot according to this expression:

sum ({1<[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>} [Reserve bedrag in €] / 1000000)

I thought that a clever way of making sure the graph would always display properly, was making use of this expression:

max(aggr(sum({1 <[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>}[Reserve bedrag in €] / 1000000),Peildatum))

But for some reason, the line goes above (and out of) the chart. Dimension in this chart is Peildatum.

When I add 4.5 mln. to the max() expression, it's OK. What am I doing wrong?

Re: Set analysis expression as dynamic max in line graph?!?

avinashelite — Wed, 23 Mar 2016 10:07:08 GMT

could you please share the problem screen shots

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 10:13:33 GMT

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 10:14:19 GMT

If you're using more dimensions in your chart then just Peildatum then include the other dimension(s) in the aggr as well.

max(aggr(sum({1 <[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>}[Reserve bedrag in €] / 1000000),Peildatum, DimX))

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 10:16:31 GMT

Hi Gysbert,

No - Peildatum is the only dimension. I use $(vPeiljaar) to show only Peildatum for a certain Peiljaar (also a field in my master calendar), but the x-axis is Peildatum...

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 10:19:44 GMT

I was afraid of that

Can you post a small qlikview document that illustrates the problem. See this document for how to do that safely: Preparing examples for Upload - Reduction and Data Scrambling‌

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 10:24:58 GMT

Uhm, reducing data messed it up seriously. I'm willing to PM / mail it to you, but I don't think I should post it here.. (It's about 18 Mb)

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 11:06:35 GMT

Well, the max upload limit is 300 MB . So 18 MB is ok.

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 11:25:26 GMT

Can I PM it? And if yes, how? (Still new on this forum...)

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 11:55:41 GMT

I thought I'd be able to pm you when we started "following" each other. But that doesn't seem to be the case. Let's try LinkedIn. If you have a LinkedIn profile you shouldn't have any trouble finding me there. The number of Hans de Vries'en on LinkedIn is rather large... So, I have no idea which one those you might be

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 12:24:55 GMT

Invited you to connect... Also mentioned my mail addy (rather cryptically, as LI forbids it.. )

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 13:47:28 GMT

I'm afraid I don't see anything wrong. It seems to work just fine. Grafiek IBNR with 2015 and Totaal selected:

Your expression for the max returns 64,94 and that works fine as max axis value for the chart.

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Wed, 23 Mar 2016 13:52:48 GMT

That is true, but that is the one in which I added 4.5 miliion:

max(aggr(sum({1 <[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>}[Reserve bedrag in €] / 1000000),Peildatum)) + 4.5

Seems rather silly to be adding a constant to an expression, just to make it 'fit', if you catch my drift...

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Wed, 23 Mar 2016 15:57:13 GMT

Ok, I see what the problem is. You're messing around with min and max settings for the x-axis. The chart actually contains more data, but doesn't show it. And you're using {1} in your expressions to overrule the selections of Peildatum and Peiljaar. That needs to be added to the expression to calculate the max value for the y-axis:

=max({<Peildatum, Peiljaar={$(vPeiljaar)}>}aggr(

sum({1 <[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>}[Reserve bedrag in €] / 1000000)

,Peildatum))

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Thu, 24 Mar 2016 06:37:46 GMT

Brilliant!! That did the trick! And - what's more important - I see your point! Thanks, Gysbert for taking the trouble for helping me out this way!!

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Thu, 24 Mar 2016 08:17:20 GMT

One more thing - I tried the same min/max thing on the chart in "Grafiek Reserve", which is a continuously shifting graph, showing the reserve for the last 365 days.. So again, I was messing about with the X-axis.. Your solution didn't work there, because vPeiljaar is not an issue there.. Any ideas on how this one should be tackled?

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Thu, 24 Mar 2016 08:39:27 GMT

You have records where Peildatum is null. You can check this by making a copy of the chart and change the chart type to a Straight Table chart. Then sort that table by Reserve in descending order.

To get the maximum that excludes the null Peildatum you can use this expression:

max({<Peildatum={'*'}>}aggr(sum({1<[Schadecategorie] = {'A','B','C','D','E','F','G','H'}>}[Reserve bedrag in €] / $(vDivideFactor)),Peildatum))

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Thu, 24 Mar 2016 08:53:31 GMT

OK, going to try that.. Shouldn't I rather try to fix this in the load script? As you can see I used IntervalMatch() (to be honest, for the first time) and I suspect I might have done something wrong there..

Re: Set analysis expression as dynamic max in line graph?!?

hansdevr — Thu, 24 Mar 2016 09:02:03 GMT

Yes, there is indeed one NULL value for Peildatum; reserve there is 124,39 (far too high)... I haven't a clue how this happened, to be honest..

Re: Set analysis expression as dynamic max in line graph?!?

Gysbert_Wassenaar — Thu, 24 Mar 2016 09:19:46 GMT

You have a number of records where the Startdatum and Einddatum have the same date (just different times). Those won't be matched with the intervalmatch. That's because the Peildatum values don't have a time fraction. So the Peildatum that you'd expect to be matched is earlier than the Startdatum of the same day, i.e. Peildatum 26-03-2016 00:00:00 lies before the interval of Startdatum 26-03-2016 18:48:17 and Einddatum 2016-03-22 18:49:57.