
Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 22, 2016 11:42 AM (in response to Marco Oberti)i found this expression, but take a min nversion value... someone know why?
sum(Aggr(SUM({<[NVERSION] = {"=Aggr(Max([NVERSION]),[polizza.CNUMPRODUCT])"}>}[NETAMOUNT]),[CNUMPRODUCT]))
thanks!

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jun 22, 2016 11:45 PM (in response to Marco Oberti)I think you might be able to achieve what you are looking for by using FirstSortedValue() function, but I am unable to offer much advice without playing around with it. Would you be able to share your qvf file to help you with the exact expression which might help you here?

Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 23, 2016 4:56 AM (in response to Sunny Talwar )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)

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jun 23, 2016 8:35 AM (in response to Marco Oberti)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?
Expression used:
Sum(Aggr(FirstSortedValue([product.AMOUNT], [product.NVERSIONE]), [product.CNUMPRODUCT], [product.STATUS]))

Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 25, 2016 4:49 PM (in response to Sunny Talwar )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
Total= 177
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


Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 25, 2016 5:02 PM (in response to Sunny Talwar )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).
then get:
product  nversion  amount //( status)
POL1 20151027 101 // 1
POL2 20151102 66 // 1
POL3 20151108 5 // 1
POL3 20151108 5 / / 2
Total= 177
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
sorry!

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jun 27, 2016 1:59 AM (in response to Marco Oberti)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

Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 28, 2016 1:37 PM (in response to Sunny Talwar )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.

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jun 28, 2016 1:48 PM (in response to Marco Oberti)Have you tried this expression in a KPI object?
Sum(Aggr(FirstSortedValue([product.AMOUNT], [product.NVERSIONE]), [product.CNUMPRODUCT], [product.STATUS]))

Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jun 28, 2016 2:18 PM (in response to Sunny Talwar )yes yes I've used it and it works properly! but in my project I do not know if I can use the status field. Unfortunately not up to me. I wanted to know if there was an expression with which calcalre the same result just relying on nversion. (With kpi component, with the table it works fine)

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jun 28, 2016 11:31 PM (in response to Marco Oberti)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]))

Re: How to filter a sum of amount for each field of a particular dimension
Marco Oberti Jul 1, 2016 4:31 AM (in response to Sunny Talwar )I tried changing the data but does not respond as I would like.
No problem, I'll try to use the first.
Thanks for everything, patience and time that you gave me!
until next time!

Re: How to filter a sum of amount for each field of a particular dimension
Sunny Talwar Jul 1, 2016 11:23 AM (in response to Marco Oberti)I wish I was able to help you better .
Best,
Sunny












