Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
bitaneyim
Contributor

Firstsortedvalue or Concat

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.2016-01-15 07_30_20-10.168.195.jpg

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:

2016-01-15 07_34_33-10.168.195.jpg

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Value in Line Chart

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

=Concat(DISTINCT

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

,', ')

8 Replies
MVP
MVP

Re: Value in Line Chart

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?

bitaneyim
Contributor

Re: Value in Line Chart

Hi swuehl,

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

Here an example:

AbrufnummerWocheAuftragsmenge
2532016/034000
2542016/038000
2552016/0310000
2562016/032000

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?

MVP
MVP

Re: Value in Line Chart

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).

bitaneyim
Contributor

Re: Value in Line Chart

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:

WocheAbrufnummerAuftragsmenge
2016/17229

7550

2016/172457550
2016/172328000
2016/172418100

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

=FirstSortedValue(Abrufnummer, -Auftragsmenge) = 241

MVP
MVP

Re: Value in Line Chart

You can also try something like

=Concat(DISTINCT

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

,', ')

bitaneyim
Contributor

Re: Value in Line Chart

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

MVP
MVP

Re: Value in Line Chart

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

=Concat(DISTINCT

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

,', ')

bitaneyim
Contributor

Re: Firstsortedvalue or Concat

Thank you very much. it works.