firstsortedvalue with filters

[LG]

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!

Thanks already 🙂

[JGMDataAnalysis · Accepted solution]

Something like this...

[LG]

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?

[JGMDataAnalysis]

[#] 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...

[LG]

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.

[JGMDataAnalysis · Accepted solution]

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.

[LG]

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

[JGMDataAnalysis]

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

[LG]

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 🙂

[LG]

Hi @JGMDataAnalysis 

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?