Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
can you provide data?
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 |
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))
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.
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))
Hi Steve,
Thanks a lot. it worked 🙂