topic Re: Issue with top 3 values with same date in App Development

Issue with top 3 values with same date

dandaanilreddy — Tue, 21 Sep 2021 14:08:04 GMT

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

Re: Issue with top 3 values with same date

PrashantSangle — Tue, 21 Sep 2021 15:39:04 GMT

can you provide data?

Re: Issue with top 3 values with same date

dandaanilreddy — Tue, 21 Sep 2021 15:49:54 GMT

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

Re: Issue with top 3 values with same date

stevejoyce — Tue, 21 Sep 2021 15:50:49 GMT

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

Re: Issue with top 3 values with same date

dandaanilreddy — Tue, 21 Sep 2021 15:55:22 GMT

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.

Re: Issue with top 3 values with same date

stevejoyce — Tue, 21 Sep 2021 16:00:05 GMT

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

Re: Issue with top 3 values with same date

dandaanilreddy — Tue, 21 Sep 2021 16:05:13 GMT

Hi Steve,

Thanks a lot. it worked 🙂