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!

[sunny_talwar]

Have you tried this expression in a KPI object?

Sum(Aggr(FirstSortedValue([product.AMOUNT], -[product.NVERSIONE]), [product.CNUMPRODUCT], [product.STATUS]))

[marcobe13]

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)

[sunny_talwar]

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

[marcobe13]

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!

[sunny_talwar]

I wish I was able to help you better .

Best,

Sunny