Track change in one column over time

[bhorneber]

Hi,

I am trying to create one specific analysis and just cannot find the correct syntax/expression to put it all together. I really appreciate all the help you can give.

This is what I have: A list of deals, identified by IDs that have a specific status and volume. Once a week the complete list of all deals is pulled from a database, which looks like this:

Deals-ID	Day	Status	Volume
ID1	02.11.	open	10'
ID1	09.11.	open	10'
ID1	16.11.	won	10'
ID1	23.11.	won	10'
ID2	02.11.	open	7'
ID2	09.11.	open	7'
ID2	16.11.	open	7'
ID2	23.11.	won	7'
ID3	16.11.	created	12'
ID3	23.11	open	12'

I now want to make an analysis how each deal has changed over time, meaning "how many deals have changed their status from 'open to won', from 'created to open', etc." (in reality there are some more).

One thing I am struggeling with is that the time frame can be defined by the user. So the start and end day of my analysis are dynamic. Getting the user input is easy, but using the variables correctly unfortunately not, at least not for me.

As an example: When the user selects 02.11. as start and 23.11. as end, the following should be displayed:

Status-Change	#ofDeals	Sum(Volume)
open to won	2	17'
null to open	1	12'

The format is not that important, a matrix would be great, too:

# of deals:

open (start)	null (start)
-	1	open (end)
2	-	won (end)

Now, by clicking on one row/box the according IDs should be selected/displayed.

I can make a list with Deal-IDs as dimension and a status column for each day using Only( {<[Day]={'$(vStartDate)'}>} [Status]) as expression. But this merely is an overview.

I also played around with the aggr-function, unfortunately without any success.

Finally I tried another format where I had one column for each day with the status, did not help either.

I really hope that some of you have an idea, how to track the status-changes of deals between two given days.

Best regards,

Benedikt

[sbaldwin]

Hi Try joining your source data to the preivous transaction, in this example i have create a list box for the date selection but you could switch this to use variables pritty easyly here is a sample script: (I also attached the .qvw)

data:

LOAD DealsID, num(Day) as Day, Status, Volume

INLINE [

    DealsID, Day, Status, Volume

    ID1, 02/11/2012, open, 10

    ID1, 09/11/2012, open, 10

    ID1, 16/11/2012, won, 10

    ID1, 23/11/2012, won, 10

    ID2, 02/11/2012, open, 7

    ID2, 09/11/2012, open, 7

    ID2, 16/11/2012, open, 7

    ID2, 23/11/2012, won, 7

    ID3, 16/11/2012, created, 12

    ID3, 23/11/2012, open, 12

];

temp:

load

min(Day) as min,

max(Day) as max

Resident data;

let v_min = peek('min');

let v_max = peek('max');

calender:

load

RowNo() +$(v_min) -1 as Day,

date(RowNo() +$(v_min) -1) as StatusChangeDate

AutoGenerate ($(v_max) - $(v_min) + 1);

drop table temp;

tempdata:

noconcatenate load *,

AutoNumber(Day,DealsID) as DealsIDOrder

Resident data

order by DealsID,Day asc

;

left join (tempdata)

load DealsID,DealsIDOrder+1 as DealsIDOrder,Status as PreviousStatus,Day as PrevousDay

Resident tempdata;

drop table data;

data:

load *,if(isnull(PreviousStatus),'Null',PreviousStatus) as temp Resident tempdata;

drop field PreviousStatus;

rename field temp to PreviousStatus;

drop table tempdata;

rename table data to tempdata;

data:

NoConcatenate load *,PreviousStatus &'->' & Status as StatusChange Resident tempdata;

drop table tempdata;

dealsummary:

load distinct DealsID,DealsID as DealsIDCounter,Volume Resident data;

drop field Volume from data;

[bhorneber]

Hi,

first, thanks a lot for your reply and for all the time you spend creating it! I really appreciate that.

Unfortunately, it seems that I was not able to fully explain what my problem is.

The solution you proposed counts all the changes between two dates. Meaning when the status of one deal changes from nothing to open, and then from open to closed, the according deal is listed in those two sections. What I try to compute is just the difference between the given start and end date, no matter what happens in between.

Nevertheless, your answer helped me a lot in getting to my solution:

I just "copied" the data table, resulting in two identical tables, linked via the dealID. All fields except the dealID are qualified.

This way I am now able to select a start date of the "start-table" and an end date of the "end-table". Using a pivot table I can easily display what I want.

So again, thanks for your answer!