Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need a measure to account for values that had a change from "100" in the previous month to a different value in the current month:
NroProposta | DtReferencia | LLR.PdCorrente | Saldo |
---|---|---|---|
410613 | 1/12/2017 | 16,3 | 165906 |
410613 | 1/1/2018 | 16,3 | 167059 |
356907 | 1/12/2017 | 100 | 9339 |
356907 | 1/1/2018 | 36,75 | 4486 |
352529 | 1/12/2017 | 6,1 | 6409 |
352529 | 1/1/2018 | 2,5 | 6433 |
Considering the table above, NroProposta 356907 is eligible because the LLR.PdCorrente went from 100 in Dec/17 to 36,75 in Jan/18
That said, I want the measure to return the sum of Saldo for this criteria (9339 when evaluated in Jan/18)
There's some more sample data and the desired result in the file attached, (the two contracts that fit this criteria are 356907 and 351972)
Thanks in advance!
Try this
Sum(Aggr(If(Above([LLR.PdCorrente]) = 100 and [LLR.PdCorrente] <> 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC)))) / Sum(Aggr(If(Above([LLR.PdCorrente]) = 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC))))
I don't see any attachment? Did you forgot to add?
Sorry, just edited the post to include attachment
Try this
Sum(Aggr(If(Above([LLR.PdCorrente]) = 100 and [LLR.PdCorrente] <> 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC)))) / Sum(Aggr(If(Above([LLR.PdCorrente]) = 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC))))
Thank you Sunny, this works great in the sample file but I'm running into some performance issues in my actual app.
Do you see any way to pull this off without using Aggr?
Use The As-Of Table