Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
what I would like is to display at the same time the largest and smallest value at the same time.
That's an example:
At the end I would like to sort by the "absolute value". Is that clear?
Here attacched my qvw.
maybe ranking
if(
rank(-
(
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
-
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
)
)=1
or
rank(
(
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
-
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
)
)=1,
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
-
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
)
Hi maxgro thanks for your contribute.
Using your formula in the expression it's impossible to display the other possible dimension too.
What I would like to do is to sort by absolute value. So in my case the order is |1,5| ; |0,6| ; |0,3| ; |0,0|.
Clearer now?
it seems you can sort by fabs of your expression descending
What do u mean by "fabs"?
I have written the expression that you have recommended but it works like your first picture.
Your suggestion is to work in the sort function? How?
thx
yes in sort tab
fabs is the absolute value
Nice! With "fabs" the sort works, but if I would like to show only for the 3 largest values in the dimension limits and than others seems doesn't work anymore.
Do you have any suggestion about?
if(
rank(
fabs(
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
-
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
)
)<=3,
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), 0), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),0), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
-
Sum({<DataMonthYear ={">=$(=Date(addmonths(Max(DataMonthYear), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(DataMonthYear),-12), 'MMM-YY'))"}>}
[Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)
)
That solve the "fabs" sorting, but is not possible to display the sum of "others". In this case only |0,0|.
is it clear what I mean?
This is my solution to display something like "others"...
Dimension:
=aggr(if(rank(fabs(Sum(Value)))<=5,Dimension),Dimension)
No flag "suppress when value is null"
Expression:
sum(Value)
Sort:
rank(fabs(Sum(Value)))
What do you think about?
It works, but maybe you have a better solution.