FirstSorted Value with filter

[Rajkumar]

Hi everyone,

Greetings,

We have stuck in one set analysis logic,needed help to achieve this.

Store Name	Rank 1	Rank 2	Rank 3	Rank 4
Walmart	Apple	LG	Panasonic	Huawei
Target	Apple	Panasonic	honor	akai
Carrefour	Apple	Huawei	honor	akai
Tesco	Panasonic	Apple	Huawei	honor
Costco	LG	Apple	Panasonic	Huawei
Kroger	Apple	LG	Huawei	honor

Expression Used: =FirstSortedValue(Brand,-aggr(Sum({<Year=, Month=,Store=,Brand=, Quarter=, Week=,YearWeek=, DATE=, DATENUM={">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} SALES),Brand,store))

when I filter Apple from brand it should show below

Store Name	Rank 1	Rank 2	Rank 3	Rank 4
Walmart	Apple
Target	Apple
Carrefour	Apple
Tesco		Apple
Costco		Apple
Kroger	Apple

But not getting above results.Any help would be appreciated. 

[lironbaram]

hi 

your expression disregards selections in the field brand 

this is why you don't get the results you want 

[Rajkumar]

can you please advise me how to change expression.

[sunny_talwar]

Can you try this

If(Sum({<Year, Month, Store, Quarter, Week, YearWeek, DATE, DATENUM ={">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} SALES) <> 0,
FirstSortedValue(Brand,-Aggr(Sum({<Year, Month, Store, Brand, Quarter, Week, YearWeek, DATE, DATENUM = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} SALES), Brand, store)))

[Rajkumar]

Thank you for reply

Sorry  not working 

[sunny_talwar]

Would you be able to share a sample where we can see a sample?

[Rajkumar]

Please find the sample data.

Store Name	Brand	Sales	Date
Walmart	Apple	600	1/2/2019
Target	Apple	700	1/2/2019
Carrefour	Apple	1000	1/2/2019
Tesco	Panasonic	900	1/2/2019
Costco	LG	1000	1/2/2019
Kroger	Apple	700	1/2/2019
Walmart	LG	400	1/2/2019
Target	Panasonic	500	1/2/2019
Carrefour	Huawei	850	1/2/2019
Tesco	Apple	800	1/2/2019
Costco	apple	800	1/2/2019
Kroger	LG	500	1/2/2019
Walmart	Panasonic	300	1/2/2019
Target	honor	200	1/2/2019
Carrefour	honor	700	1/2/2019
Tesco	Huawei	600	1/2/2019
Costco	Panasonic	600	1/2/2019
Kroger	Huawei	400	1/2/2019
Walmart	Huawei	100	1/2/2019
Target	akai	50	1/2/2019
Carrefour	akai	300	1/2/2019
Tesco	honor	500	1/2/2019
Costco	Huawei	500	1/2/2019
Kroger	honor	200	1/2/2019

[sunny_talwar]

This seems to work

Rank 1 expression

=If(Sum({<[Store Name], Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>}Sales) = Max({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} TOTAL <[Store Name]> Aggr(Sum({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>}Sales), [Store Name], Brand)),
FirstSortedValue({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} Brand,-Aggr(Sum({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} Sales), Brand, [Store Name])))

Rank 2 expression

=If(Sum({<[Store Name], Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>}Sales) = Max({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} TOTAL <[Store Name]> Aggr(Sum({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>}Sales), [Store Name], Brand), 2),
FirstSortedValue({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} Brand,-Aggr(Sum({<[Store Name], Brand, Date, DateNum = {">=$(=$(vCYFromDate))<=$(=$(vCYToDate))"}>} Sales), Brand, [Store Name]), 2))

and so on...