Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a variable I've developed with a different tool that is the equivalent of
Sum(CurrMonBal - PrevMonBal) Where CurrMonEnteredIndicator = "N".
I'd like do accomplish that in Qlik Sense, but I'm getting stuck on the subtraction of CurrMonBal and PrevMonBal within a single equation. I've written the below set analysis to demonstrate the direction I'm trying to go, but as you'll see it calculates:
(Sum(CurrMonBal) Where CurrMonEnteredIndicator = "N") - Sum(PrevMonBal).
This does not give me accurate results. Is there a way to accomplish my initial equation with the below data.
Sum({$< [LPM.Year Month] = {"$(=Max([LPM.Year Month]))"}, [LPM.Note Entered This Month Indicator] = {'N'} >}[LPM.Note Bank Share Ledger Balance]) - Sum({$< [LPM.Year Month] = {"$(=Max([LPM.Year Month])-1)"} >}[LPM.Note Bank Share Ledger Balance])
I believe you also need the (CurrMonEnteredIndicator = 'N') condition for the second sum, something like:
Sum({$< [LPM.Year Month] = {"$(=Max([LPM.Year Month]))"}, [LPM.Note Entered This Month Indicator] = {'N'} >}[LPM.Note Bank Share Ledger Balance]) - Sum({$< [LPM.Year Month] = {"$(=Max([LPM.Year Month])-1)"}, [LPM.Note Entered This Month Indicator] = {'N'} >}[LPM.Note Bank Share Ledger Balance])
Would that work?
The syntax is ok and the measure returns results, but they are incorrect. I've QA'd against an already existing measure in a separate application that we are moving away from.
My formula here takes the sums the balance of the current month where indicator equals N and then subtracts the balance of the previous month. I'd like the formula to sum the result of current month minus previous month and THEN consider the indicator.
Initially I assumed both methods would return the same results. But I'm able to test both ways in the other application (Web Intelligence) and confirm that they indeed produce different results.
Phin, thank you for the suggestion - I gave it a try with no success though. Results are still incorrect.