topic Re: Previous and Current month data comparison in QlikView

Previous and Current month data comparison

Fri, 06 Nov 2015 10:30:31 GMT

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

Re: Previous and Current month data comparison

Gysbert_Wassenaar — Tue, 10 Nov 2015 14:23:59 GMT

Can you post a small qlikview document with example data?

Re: Previous and Current month data comparison

miguelbraga — Tue, 10 Nov 2015 14:39:58 GMT

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,

Re: Previous and Current month data comparison

Mark_Little — Tue, 10 Nov 2015 14:44:29 GMT

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

Re: Previous and Current month data comparison

Wed, 11 Nov 2015 08:26:09 GMT

Apologies for responding this late, heres some sample data:

Re: Previous and Current month data comparison

jonathandienst — Wed, 11 Nov 2015 09:50:26 GMT

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.