Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I facing an issue to know the value which has max count, let me explain you by example.
Input:
Month | Cost |
Jan | 10 |
Jan | 11 |
Jan | 11 |
Jan | 13 |
Jan | 12 |
Feb | 11 |
Feb | 12 |
Feb | 12 |
Feb | 12 |
Feb | 13 |
Output :
Month | max( aggr( count(Cost),Month,Cost)) | Max Cost Value |
Jan | 2 | 11 |
Feb | 3 | 12 |
I can able to achieve the max count but I am facing an Issue finding the max cost value. Can you please let me know how can I achive this? Thank in advance.
Regards,
Siva
How about this :
expression : FirstSortedValue(distinct Cost,-aggr( count(Cost),Month,Cost),1)
I tried this in my qvw, it works.
May be this?
Max(Cost,2)
Hi Siva,
If you can accepted that adding some scripts in back-end, then I found a method, but I think it's not good, I'm trying to find another way.
My Script:
T1:
LOAD * INLINE [
Month, Cost
Jan, 10
Jan, 11
Jan, 11
Jan, 13
Jan, 12
Feb, 11
Feb, 12
Feb, 12
Feb, 12
Feb, 13
];
T2:
NoConcatenate
LOAD Month,Cost,
Count(Cost) as num
Resident T1
Group by Month,Cost;
Left Join(T1)
LOAD Month,Cost,max(num) as num Resident T2 Group By Month,Cost;
DROP Table T2;
The expression : FirstSortedValue(distinct Cost,-num,1)
Thanks
Aiolos
Thanks for your reply Anil. It is showing as below which is incorrect. Please find the attachement.
Thanks for your reply uacg0009As you said I need in Pivot table script it self. Adding another table will become performance Issue in our case.
Regards,
Siva
Actually you can see my data model, I didn't add table, I just add one column finally.
But It will takes some times to make this column, so if your data is too large, it will affect your loading time I think.
If you can't accept this method, maybe I need to try to use another front-end way to make it.
Thanks.
Aiolos
Yes Aiolos, Table has some millions of data adding another field will cause performance. If we don't have any front end way then I can go with backend script.
Thanks,
Siva
How about this :
expression : FirstSortedValue(distinct Cost,-aggr( count(Cost),Month,Cost),1)
I tried this in my qvw, it works.
You can see the attachment, using the expression like I said.
Seems like it is working, I will do further testing and let you know. Thank you uacg0009