22 Replies Latest reply: Mar 24, 2016 5:43 AM by Hans de Vries

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

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?!?

could you please share the problem screen shots

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

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?!?

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?!?

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?!?

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?!?

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

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

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

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

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?!?

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?!?

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?!?

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?!?

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?!?

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?!?

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?!?

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?!?

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?!?

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?!?

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.

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

I understand.. But how can this be remedied? Alterations in reserve can indeed occur multiple times per day..

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

Well, that depends on what you want to report per peildatum. Do you need a value of something only for the last transaction of the day? Or can you simply sum or count the measures even if there are multiple transactions per day?

Also, have a look at this blog post: Creating Reference Dates for Intervals. This may be a more convenient way to create the peildatum dates. In any case you should probably use the floor function on Startdatum to make sure you have a date that starts at midnight.

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

I'll send you an explanation per mail (in Dutch)..