How to filter a sum of amount for each field of a particular dimension

[marcobe13]

Hello everyone! I have two questions for this community, I hope you can help me

FIRST QUESTION:

I created an app to calculate the sum of the final amount.

But I need to do it once for each numproduct where nversion is the maximum number.

EX --> i want:

product    | nversion     |  amount      (not considerer - status)

POL1        20151027      100                                     1

POL2        20151102       66                                      1

POL3        20151108       10                                      1+2

TOTAL = 176

how can I do?

I would like to do in the sheet not being loading data!

SECOND QUESTION:

I can show in a table only the results of the distinct?

I want to use as a dimension only on the results of a distinct product.

then associate with each dimension his nversion greater. If that nversion has two states, and I want to see only the status corresponding to 1?

  Former: ---> I would like to

product    | nversion     |  amount  | status

POL1        20151027      100         1

POL2        20151102       66          1

POL3        20151108       5            1

TOTAL = 171

I read every forum thread on this topic but I could not find a solution!

thanks!

[marcobe13]

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!

[sunny_talwar]

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?

[marcobe13]

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)

[sunny_talwar · Accepted solution]

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]))

[marcobe13]

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

[sunny_talwar]

I guess I still don't understand by 20151027 = 100 and not 101?

Also I am not sure why Status need to be removed? It almost seems like we are looking at two different applications. If its not too difficult, do you want to try explaining one more time

[marcobe13]

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!

[sunny_talwar]

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

[marcobe13]

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.