Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LG
Contributor III
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 PlantMaterial NumberMaterial DescriptionDirect Savings YearInvoice Amount CHFInvoice QuantityDiff. prev. YearCalc. Direct Savings CHFSavings / CostVendor Category
1Germany12345Apple2019                          480’250.86163’681-0.4802                               -115’571.35SavingsR
2Germany12345Apple2019                              3’952.291’125-0.4802                               -115’571.35SavingsM
3Germany12345Apple2018                          694’002.40240’158-0.4802                               -115’571.35SavingsR
4Germany12345Apple2018                                  940.13503-0.4802                               -115’571.35SavingsM

 

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 🙂

Labels (2)
2 Solutions

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

Something like this...

CommunityQlik_Measure.PNG

View solution in original post

JGMDataAnalysis
Creator III
Creator III

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

QlikCommunity_AggrFunction.png

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.

View solution in original post

10 Replies
JGMDataAnalysis
Creator III
Creator III

Something like this...

CommunityQlik_Measure.PNG

LG
Contributor III
Contributor III
Author

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

2020-04-07 07_35_05-Start.png

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
Creator III
Creator III

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

CommunityQlik_SetModifiersWithAdvanceSearches.PNG

LG
Contributor III
Contributor III
Author

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

2020-04-07 16_01_29-Start.png

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
Creator III
Creator III

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

QlikCommunity_AggrFunction.png

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
Contributor III
Contributor III
Author

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:

2020-04-08 10_29_32-Start.png

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

2020-04-08 10_32_36-Start.png

JGMDataAnalysis
Creator III
Creator III

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

QlikCommunity_Script.PNG

LG
Contributor III
Contributor III
Author

Your file was a big help.

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

PlantNumberDescriptionPeriodRankSavingsVendor CategoryRow
Switzerland100007Banana201920Injection12394
Switzerland100007Banana201910Raw12394
China100007Banana2019126’968Raw21333
Poland100007Banana201920Inter21883
Poland100007Banana201910Raw21883

 

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
Contributor III
Contributor III
Author

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?