Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I'm trying to show top 3 from the list
for example
Field ,Value
a - -9
b--8
c--6
d- 1
Expecting O/p in Text box as
Text box with top 3 should take the top 3 positive values,If there are no positive values anything should be dispalyed,As from the above data my top 3 will show a:1
For Bottom 3 it should always consider the top negative values
a - -9
b--8
c--6
Please share your views
Thanks
Sowmya
What are you looking to get? I guess what is wrong with the output?
I'm expecting o/p like above image ,but with the expression i postes it is giving me like below
Try this... hopefully I got all the parenthesis right 🙂
=Concat(Aggr(
If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
[Material Brand]
& ' | ' &
(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
& Chr(10) &
(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
)
, [Material Brand]))
It is not sorting
May be try this
=Concat(Aggr(
If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
[Material Brand]
& ' | ' &
(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
& Chr(10) &
(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
)
, [Material Brand]), Aggr(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))), [Material Brand]))
or this
=Concat(Aggr(
If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
[Material Brand]
& ' | ' &
(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
& Chr(10) &
(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
)
, [Material Brand]), -Aggr(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))), [Material Brand]))
for the Below expression i am trying to give number format when the value is greater than 1000 this works fine ,How to give format when the value is<=100
Concat(If(Aggr(Rank(TOTAL Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value])),
[Material Brand]) <=3, [Material Brand] & ' | ' & Replace(num(Aggr(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value), [Material Brand])/1000,'# ##0K','.',' '),' ',',')),
Chr(10), Aggr(Rank(TOTAL Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value])), [Material Brand]))
What are the two formats you are looking to get?
If my value is >1000 it should show something like 23k,if <=100 show 230
Expression is already fairly complex, I don't think I will be able to give you a working expression without testing it out... but you can check for the idea from here -> Format Number (in Thousand)