Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

william_fu
Contributor II

Measure that compares parameter from last month

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:

NroPropostaDtReferenciaLLR.PdCorrenteSaldo
4106131/12/201716,3165906
4106131/1/201816,3167059
3569071/12/20171009339
3569071/1/201836,754486
3525291/12/20176,16409
3525291/1/20182,56433

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!

1 Solution

Accepted Solutions

Re: Measure that compares parameter from last month

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


Capture.PNG

5 Replies

Re: Measure that compares parameter from last month

I don't see any attachment? Did you forgot to add?

william_fu
Contributor II

Re: Measure that compares parameter from last month

Sorry, just edited the post to include attachment

Re: Measure that compares parameter from last month

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


Capture.PNG

william_fu
Contributor II

Re: Measure that compares parameter from last month

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?

Re: Measure that compares parameter from last month

Community Browser