Firstsortedvalue or Concat

[tiklabaq]

Hi,

I need your help.

I want to show in the Line Chart some value. For example, order quantities. The maxstring in a week and the minstring a week.

The expression are:

MaxString(total <Woche> {<Abrufwoche, Abrufnummer=>} (Auftragsmenge))

MinString(total <Woche> {<Abrufwoche, Abrufnummer=>} (Auftragsmenge))

Now I want to show in the Chart, which "Abrufnummer", that means customer order is the max and which order is min.

Like:

How can I do this? I don't know how I can find the order number and how I can show it in the chart.

[swuehl]

Could you post some sample lines of data or a small sample QVW? It's unclear to me how your data model looks like.

For example, will the "Abrufnummer" be the same across all Woche for the Maximum / Minimum line or changing?

[tiklabaq]

Hi swuehl,

thx for your answer. First I need to know, how can I get the Abrufnummer.

Here an example:

Abrufnummer	Woche	Auftragsmenge
253	2016/03	4000
254	2016/03	8000
255	2016/03	10000
256	2016/03	2000

so the result should be for Auftragsmenge in the chart:

Minimum: 2000

Maximum: 10000

This result I display with the maxstring expression.

Now I want to display the Abrufnummer for Minimum and Maximum. In this case:

Abrufnummer:

Minimum: 256

Maximum: 255

Can you say me, how I can make this?

[swuehl]

You can use FirstSortedValue for this:

Minimum:

=FirstSortedValue(DISTINCT Abrufnummer, Auftragsmenge)

Maximum:

=FirstSortedValue(DISTINCT Abrufnummer, -Auftragsmenge)

And instead of MaxString() / MinString(), I would use Min() / Max() for numeric values (Auftragsmenge).

[tiklabaq]

Thx a lot. It is working.

But I have a problem: DISTINCT is not available and if I have more then one Abrufnummer for Auftragsmenge, I get o Null.

Like this:

Woche	Abrufnummer	Auftragsmenge
2016/17	229	7550
2016/17	245	7550
2016/17	232	8000
2016/17	241	8100

=FirstSortedValue(Abrufnummer, Auftragsmenge) = - (NULL)

=FirstSortedValue(Abrufnummer, -Auftragsmenge) = 241

[swuehl]

You can also try something like

=Concat(DISTINCT

Aggr( If(Auftragsmenge = Min(TOTAL<Woche> Auftragsmenge), Abrufnummer), Woche, Auftragsmenge)

,', ')

[tiklabaq]

the same problem, if there is only one Abrufnummer I get the Abrufnummer, but if there are more then one, it's not working.

[swuehl · Accepted solution]

Sorry, you need to use Abrufnummer in the aggr() dimensions

=Concat(DISTINCT

Aggr( If(Auftragsmenge = Min(TOTAL<Woche> Auftragsmenge), Abrufnummer), Woche, Abrufnummer)

,', ')

[tiklabaq]

Thank you very much. it works.