Select items which changed a field value from A to... - Qlik Community

Report Inappropriate Content · ‎2014-03-11

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.

Report Inappropriate Content · ‎2014-03-11

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

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

Lukasz

Report Inappropriate Content · ‎2014-03-11

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?

Report Inappropriate Content · ‎2014-03-11

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)

Report Inappropriate Content · ‎2014-03-11

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.

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

Thanks

AJ

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