Track status over time

[#1]

Hi,

I'm new to the forum and have worked with Qlikview for a few months now, but I am a bit stuck on the following.

I am busy pulling in data from users that have a status assigned to them.

The status can change over time and I would like to track these changes as well as track from what the old status was and what the new status is. In that way I can see from what status the user moved.

A sample data set would be something like:

Date	User_ID	Status
2016-02-08	XXX	1
2016-02-08	YYY	2
2016-02-08	ZZZ	3
2016-02-15	XXX	3
2016-02-15	YYY	5

I pull the data on a weekly basis so Week on Week or Month on Month reporting will be the end result.

Thanks in advance!

[Gysbert_Wassenaar]

Perhaps something like this:

Temp:

LOAD User_ID, Date, Status FROM ....;

Result

LOAD

     User_ID,

     Date,

     Year(Date) as Year,

     Month(Date) as Month,

     Date(MonthStart(Date),'YYYYMM') as YearMonth,

     Week(Date) as Week,

     Status,

     Previous(Status) as [Previous Status]

RESIDENT Temp

ORDER BY User_ID, Date;

Drop Table Temp;

For Month on Month you'll have to decide first what you want to compare, first status of the month to first status of the month or last to last or last of previous month to first of current month.

[#3]

Thanks for the reply!

Just to clarify, will this look at the previous status for the specific user_id?

[Gysbert_Wassenaar]

Ah, no. That's need a little change: If(User_ID=Previous(User_ID), Previous(Status)) as [Previous Status]

[#5]

Thanks!

I will give it a try.

The idea is that I can look at the previous state of the user_id although the records will not follow on each other.

So basically comparing the newest record for the user with the previous one.

[MarcoWedel · Accepted solution]

Hi,

maybe helpful:

tabUserStatus:
LOAD Date(MakeDate(2015)+Ceil(412*Rand())) as Date,
    'User'&Ceil(Rand()*20) as User_ID,
    Ceil(Rand()*10) as Status
AutoGenerate 100;

Left Join (tabUserStatus)
LOAD User_ID,
    Date,
    AutoNumber(Date, User_ID) as StatusSeq
Resident tabUserStatus
Order By Date;

Left Join (tabUserStatus)
LOAD User_ID,
    StatusSeq + 1 as StatusSeq,
    Status as LastStatus
Resident tabUserStatus;

Left Join (tabUserStatus)
LOAD User_ID,
    StatusSeq,
    Text(If(StatusSeq>1,LastStatus&' -> ')&Status) as [Status From/To]
Resident tabUserStatus;

regards

Marco

[brijesh1991]

It's excellent; Can we have a visual chart for the same; like having a line joining all status of users (Different color lines for different users.)

[brijesh1991]

It's excellent; Can we have a visual chart for the same; like having a line joining all status of users (Different color lines for different users.)