Hello! thanks for the reply! This is the app I'm using!
Hold the filter in October 2015, should be the data that interest me.
The result I would get is 176, why should take nversion 20,151,004 amount with 66, not 20,151,002 (smaller)
Is this what you want?
But I still get the total of 177 because 20151027 is 101 for POL1. Can you check if 177 is the right total still?
Sum(Aggr(FirstSortedValue([product.AMOUNT], -[product.NVERSIONE]), [product.CNUMPRODUCT], [product.STATUS]))
the result is correct, but there is a problem.
I thought so at a similar solution, but it works only because nversion 20151008 has two nversion equal with two different statuses.
Then 101 + 66 + (status1 (5) + Status2 (5)) = 177
the expression takes the first result of the filter.
But if I take off [product.STATUS] by the expression, the result becomes 167.
Sum (Aggr (FirstSortedValue ([product.AMOUNT] - [product.NVERSIONE]), [product.CNUMPRODUCT]))
I would like to have the result 177 although not using the STATUS parameter.
add up the amounts where nversion is greater for each product.
product | nversion | amount ( status)
POL1 20151027 101 1
POL2 20151102 66 1
POL3 20151108 5 1
POL3 20151108 5 2
with the current expression if I remove status, it takes one of only two nversion.
because, the two nversion are equal, and the expression takes the first (one of two). I need both of them
there is a solution?
p.s: sorry for the first message, I was wrong amount, was 66 not 64
I'm sorry I hurt copied the table from the first message. Now I corrected.
the concept is that I would get that result without using the status filter (not sure if I'll use it)
I want to find the maximum nversion without using the status. The pol 3 product has two equal maximum nversion, because his nversion has two states.
I would like to be able to identify the maximum nversion for each product, without using the status as a filter. If there are two equal nversion, both the high (it is implied that there are two different states in two equal nversioni).
product | nversion | amount //( status)
POL1 20151027 101 // 1
POL2 20151102 66 // 1
POL3 20151108 5 // 1
POL3 20151108 5 / / 2
I was able to explain better? apologizes for the errors in the question messages
What I was wondering last message I was correct, I just wrong numbers
You can get this without using Status as your dimension:
product | nversion | amount
POL1 20151027 101
POL2 20151102 66
POL3 20151108 10
or this with status as dimension or another field that can be used to uniquely define the two rows....
product | nversion | status | amount
POL1 20151027 1 101
POL2 20151102 1 66
POL3 20151108 1 5
POL3 20151108 2 5
Yes, it's true! thanks for everything!
if I may ask one last thing ..
if I wanted to use the formula in a KPI without status the result becomes 167. Is there a way as it will produce 177 in a KPI box component?
the firstsortedvalue ignores the nversion 20151108 expression without status filter, because I think there are two identical results.
If that is what you are looking for, try this. Not sure if this will always work due to use of Amount in Aggr() function, but you can try
Sum(Aggr(If(Max(TOTAL <[product.CNUMPRODUCT]> Aggr([product.NVERSIONE], [product.CNUMPRODUCT], [product.AMOUNT])) = Avg([product.NVERSIONE]), Sum(product.AMOUNT)), [product.CNUMPRODUCT], [product.AMOUNT]))