# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## firstsortedvalue with filters

I read many other posts, but I can't apply it to my case...

I think "firstsortedvalue" might be the solution.

Here's my Data (I gave my best to get a good example):

 # Reporting Plant Material Number Material Description Direct Savings Year Invoice Amount CHF Invoice Quantity Diff. prev. Year Calc. Direct Savings CHF Savings / Cost Vendor Category 1 Germany 12345 Apple 2019 480’250.86 163’681 -0.4802 -115’571.35 Savings R 2 Germany 12345 Apple 2019 3’952.29 1’125 -0.4802 -115’571.35 Savings M 3 Germany 12345 Apple 2018 694’002.40 240’158 -0.4802 -115’571.35 Savings R 4 Germany 12345 Apple 2018 940.13 503 -0.4802 -115’571.35 Savings M

What do I want to do:

There are multiple same values for "Calc. Direct Savings CHF", but I only want to see the "Vendor Category", based/ranked on the <highest> "Invoice Amount CHF" per "Direct Savings Year"

Example:

#1, "Vendor Category" <R> would be shown, because the "Invoice Amount CHF" for 2019 is higher than #2

#3, "Vendor Category" <R> would be shown, because the "Invoice Amount CHF" for 2018 is higher than #4

Finally, out of the 4 lines, only 2 shall remain --> #1 & #3

hope this makes sense!

Labels (2)

• ### qlik sense

2 Solutions

Accepted Solutions
Highlighted
Creator II

## Re: firstsortedvalue with filters

Something like this...

Highlighted
Creator II

## Re: firstsortedvalue with filters

What is the "Sum({1}" (at the beginning) and the  = 1"} (at the end) doing? There was no like no change when I removed it.

The expression works as follows...

For the expression to work you have to refer to a field that uniquely identifies each record in the table. The [Vendor Group.Vendor Category] field does not serve this purpose.

Also, I suggest you add the NoDistinct qualifier to the Aggr function: Aggr(NoDistinct...

Try those modifications and then tell me.

10 Replies
Highlighted
Creator II

## Re: firstsortedvalue with filters

Something like this...

Highlighted
Contributor III

## Re: firstsortedvalue with filters

Can't make it run at all (sorry, the datamodel has other names than I've posted in the table...):

Sum(
{<
[#] = {"=Aggr(Rank(Sum({1} [InvRec.ReceiptAmount_CHF])), [CalcDirectSavings.Calc Direct Savings Period], [Vendor Group.Vendor Category]) = 1"}
>}
[CalcDirectSavings.Calc Direct Savings CHF]
)

What is the [#] doing?

Doesn't the Aggr(Rank... between "" becomes text?

Highlighted
Creator II

## Re: firstsortedvalue with filters

[#] is the name of the field that works as ID in the example you provided. You have to change [#] to a field in the model that uniquely identifies the records.

Doesn't the Aggr(Rank... between "" becomes text?

No, it is an advanced search...

Highlighted
Contributor III

## Re: firstsortedvalue with filters

Almost solved and I think this is the right way, I've used this code:

Sum({<[Vendor Group.Vendor Category] = {"=Aggr(Rank(Sum({1} [InvRec.ReceiptAmount_CHF])), [CalcDirectSavings.Calc Direct Savings Period], [Vendor Group.Vendor Category]) = 1"}>}
[CalcDirectSavings.Calc Direct Savings CHF])

1) Solved: When I  select some filters and there are multiple "Vendor Group.Vendor Category", everything is working fine

2) New Issue: When there is only 1 "Vendor Group.Vendor Category" per "Material Number", the calculation is showing 0. Doesn't matter what filter is selected

3) New Issue: When no filter is selected, all calculation are showing 0, even the ones which work fine in 1) Solved

I tried playing around... but couldn't figure it out...

What is the "Sum({1}" (at the beginning) and the  = 1"} (at the end) doing? There was no like no change when I removed it.

Highlighted
Creator II

## Re: firstsortedvalue with filters

What is the "Sum({1}" (at the beginning) and the  = 1"} (at the end) doing? There was no like no change when I removed it.

The expression works as follows...

For the expression to work you have to refer to a field that uniquely identifies each record in the table. The [Vendor Group.Vendor Category] field does not serve this purpose.

Also, I suggest you add the NoDistinct qualifier to the Aggr function: Aggr(NoDistinct...

Try those modifications and then tell me.

Contributor III

## Re: firstsortedvalue with filters

We are getting closer, but still not working ☹️  (thanks for the explanation, at least I think I do understand it now).

I've used this code:

Sum(
{<[CalcDirectSavings.%Material|Source] =
{"=Aggr(NoDistinct Rank(Sum({1} [InvRec.ReceiptAmount_CHF])), [CalcDirectSavings.Calc Direct Savings Period], [Vendor Group.Vendor Category], [CalcDirectSavings.%Plant])=1"}>}

[CalcDirectSavings.Calc Direct Savings CHF])

It's more or less saying (in my words): "rank by InvRec.Receipt Amount ({1} doesn't matter what filter selected) and calculated by "Period (year), Category, Plant", then calculate the "Calc Direct Savings CHF" and only keep the rank 1.

I figured out that I need a new one ([CalcDirectSavings.%Plant]),  because it's: Calculate -> "Material Number per Plant per Year... Sorry I didn't see that before.

I changed the Unique Identifier [#] to [CalcDirectSavings.%Material|Source], this one is looking for the "Material Number", which should be the most Unique one? I've created a new Column saying Material|Plant|Source, but for now it's making the results even worse.

Remaining Issue:

With the "Code" I've used, the "Material Number = 111142" is showing up correctly.

But the "Material Number = 100007 & 9476" are giving the Result 0.

111142 is only used by 1 Plant

100007 & 9476 are used by multiple Plant

I've made a new Table how it should look like, see last Column "Logic..."

Highlighted
Creator II

## Re: firstsortedvalue with filters

I attach a .qvf file from a test application.

I don't know the data model of your application but why don't you just add a [ID] field like this ...

Highlighted
Contributor III

## Re: firstsortedvalue with filters

Your file was a big help.

I applied the "Rank" & "Row (ID)" (+ another filter for "Material" & "Plant") logic to my table / datamodel:

 Plant Number Description Period Rank Savings Vendor Category Row Switzerland 100007 Banana 2019 2 0 Injection 12394 Switzerland 100007 Banana 2019 1 0 Raw 12394 China 100007 Banana 2019 1 26’968 Raw 21333 Poland 100007 Banana 2019 2 0 Inter 21883 Poland 100007 Banana 2019 1 0 Raw 21883

The Code I've used:

=Sum(
{<
[Calc.DirectSavings.Row Number] = {"=Aggr(NoDistinct Rank(Sum({1} [InvRec.ReceiptAmount_CHF])), [CalcDirectSavings.%Plant], [CalcDirectSavings.Calc Direct Savings Period], [CalcDirectSavings.%Material|Source], [Vendor Group.Vendor Category]) = 1"}
>}
[CalcDirectSavings.Calc Direct Savings CHF]
)

The "Rank" is working correct.

But the "Savings" is still calculated with "0". What is showing up now, the "Row" (like "12394") is duplicated... So I think the problem might be in the datamodel itself... unfortunately...

@JGMDataAnalysis  I'll do further investigation after the long weekend (easter holidays). I'll for sure send another update.

Thanks you very much! At least I've learned a lot so far 🙂

Highlighted
Contributor III

## Re: firstsortedvalue with filters

With the Code from the previous post, the "Rank" was working correctly - but I couldn't make the calculation work...

Finally I made it, using this code:

=if(Aggr(NoDistinct Rank(Sum({1} [InvRec.ReceiptAmount_CHF])), [CalcDirectSavings.%Plant], [CalcDirectSavings.Calc Direct Savings Period], [CalcDirectSavings.%Material|Source], [Vendor Group.Vendor Category]) = 1,
sum([CalcDirectSavings.Calc Direct Savings CHF]))

What do you think :)? Good?

Maybe a new Question. Do you know why the new Code doesn't work in Bar Chart?