Select items which changed a field value from A to B from one month to another

[#1]

Hi all,

I have a table where I load a list of assets and their status month on month.

The table headers could be as follow:

DATE, ASSET ID, VALUE, STATUS

Mar13, House1, 1.000.000, Rented

Mar13, House2, 2.000.000, Not Rented

Apr13, House1, 1.200.000, Not Rented

Apr13, House2, 1.900.000, Not Rented

This is just a sample, in the real case I will have millions of assets and a lot of different STATUS fields with different meanings.

Let suppose that I want to identify and analyse the set of assets that where rented in Mar13 and not rented in Apr13.

How would you select this set?

Thank you.

[#2]

Set analysis expression for you requirement would look similar to this:

{$<Status = {'Rented'}, Date = {'Mar13'}> * <Status = {'Not rented'}, Date = {'Apr13'}>}

Lukasz

[#3]

Is there any way to select this combination?

Because I would like all the chart and the content of qlikview to show numbers according to this selection.

How do I select this set analysis status?

[#4]

If you need sum, it would be. It should return combination of 'Rented' in Mar 13 AND 'Not Rented' in Apr 13

SUM({$<Status = {'Rented'}, Date = {'Mar13'}> * <Status = {'Not rented'}, Date = {'Apr13'}>} VALUE)

[#5]

Hey Marco,

Check out the attached file and see if it helps.

I created a field called Flag on back-end which has three values based on following rules.

Asset	Month	Flag
H1	Feb 2014	Beginning
H1	March 2014 (Rent)	Y
H1	Apr 2014 (NonRent)	N
H1	May 2014 (NonRent)	N
H1	Jun 2014 (Rent)	Y

Hope this trend makes sense. You can tweak the code per your needs.

Thanks

AJ