Issue with top 3 values with same date

[dandaanilreddy]

Hello All,

I'm facing issue with top 3 values when i have same date for 2 records. Below is the output am getting only 2 records.

I observed for 2021-03-31 i see there are 2 rows with different values. (300, 150)

Month name	date	product	value
March	2021-03-16	Test1	500
March	2021-03-05	Test2	410

Expected output:

Monthname	date	Company	value
March	2021-03-16	Test1	500
March	2021-03-05	Test2	410
March	2021-03-31	Test3	300

Measure expression: if(aggr(sum(value)),Monthname,row)<=3,sum(value))

Thanks

[PrashantSangle]

can you provide data?

[dandaanilreddy]

date	Product	value
2021-03-16	Test1	500
2021-03-05	Test2	410
2021-03-31	Test3	300
2021-03-31	Test3	150
2021-03-16	Test1	100

Expected output:

date	Company	value
2021-03-16	Test1	500
2021-03-05	Test2	410
2021-03-31	Test3	300

[stevejoyce]

is "row" actually a field name in your data model (unique identifier)?

Aren't you missing a rank function as well?  Your measure expression is just if sum(value) <=3.

You have more closing parenthesis then open, so something is wrong with your measure expressoin.

if(aggr(rank(sum(value)),Monthname,row)<=3,sum(value))

[dandaanilreddy]

Hi Steve,

Sorry i missed the rank while typing. I have tried the same expression what you provided but its not giving me the correct result ( giving me only 2 rows) as my dates and products are repeating multiple times with different values.

[stevejoyce · Accepted solution]

Not sure what row field is, so i switched it with date.  And i added rank parameter to break ties.

if(aggr(rank(sum(value), 1,1),Monthname,date)<=3,sum(value))

[dandaanilreddy]

Hi Steve,

Thanks a lot. it worked 🙂