topic Track status over time in QlikView

Track status over time

Mon, 15 Feb 2016 12:52:52 GMT

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!

Re: Track status over time

Gysbert_Wassenaar — Mon, 15 Feb 2016 18:09:48 GMT

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.

Re: Track status over time

Tue, 16 Feb 2016 09:31:20 GMT

Thanks for the reply!

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

Re: Track status over time

Gysbert_Wassenaar — Tue, 16 Feb 2016 11:03:24 GMT

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

Re: Track status over time

Tue, 16 Feb 2016 11:18:45 GMT

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.

Re: Track status over time

MarcoWedel — Wed, 17 Feb 2016 00:00:31 GMT

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

Re: Track status over time

brijesh1991 — Fri, 04 Mar 2016 10:00:15 GMT

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.)

Re: Track status over time

brijesh1991 — Fri, 04 Mar 2016 10:00:24 GMT

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.)