Previous and Current month data comparison

[#1]

Good day Community,

Please assist me, I’ve been cracking my skull over this:

In my data set I have a field class the holds several outcomes, but I’m interested in the two outcomes legal and not legal

I want to sum the total amount when the following conditions are satisfied:

- in the previous month, the account number's class was not legal

- and current month, the account number's class is legal

In summary I want to sum total amount for only the account number that have transitioned from not legal (in the previous month) to legal (current month)

Your assistance will be highly appreciated.

Regards,

Mbini

[Gysbert_Wassenaar]

Can you post a small qlikview document with example data?

[miguelbraga]

Hey there,

You should make a flag that indicates the number of account number's that aren't legal and those who are legal like this:

          if([account desctiption] = 'not legal', 1, 0) as flagNotLegal,

          if([account description] = 'legal', 1, 0) as flagLegal

Then in a chart make something like this:

=

sum({$<AccountNumber =, Month = {$(=(num(month(addmonths(max(date($(=Max(Year)) & '-' & num($(=max(num(Month))),'00') & '-' & '01', 'YYYY-MM-DD')),-1)))))} >} flagNotLegal)

+

sum({$<AccountNumber =, Month = {$(=max(num(Month))}>} flagLegal)

Hope it helps

Regards,

MB

[Mark_Little]

Hi,

As Gybert has suggested an example would be helpful.

With the information you have supplied I would be looking at a flag in the data as the script.

Would need to make an aggregated table ordered by date, then use the peek() or Previous() function to flag the relevant rows of data.

Then SUM({<Flag=1>}Value)

Mark

[#5]

Apologies for responding this late, heres some sample data:

[jonathandienst]

I am not sure what you expect anyone to do with these screenshots...

You need to upload a .qvw document containing sample data so that someone can assist you with your expression.