Help needed on history data with deltas/diffs, to calculate complete totals

[#1]

Hi all,

I have a set of Data I imported which contains data in this format:

UID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	A	2010-02-02	Some info 1	Some info 2
002	2	A	2010-02-02	Some info 1	Some info 2
003	1	B	2010-02-05	Some info 123	Some info 245
004	3	A	2010-02-05	Some info ABC	Some info DEF

Essentially, it contains the changes that are happening to a clients table, keeping the history data for the client changes (active, inactive).

Based on this table I need to extract the active clients for the dates where the updates occurred.

My problem is that I only have the changes (diffs or deltas as I usually refer to) and I can't figure out a way to calculate the number of clients which are active for a given date.

What happens is that if I select just one of the dates I get the count for that date, which will only contain those that were updated on that date and no the ones that are active but didn't have a change on that date (which is most of them).

I was wondering if someone could help me out on this.

The output should be something similar to this:

2010-02-02 2010-02-05

Active 80 85

Inactive 10 5

What I'm getting after several attempts will eventually get similar to this:

2010-02-02 2010-02-05

Active 80 5

Inactive 10 -

[#2]

As I believe the solution should be the same, I'll post also another example that I'm working on and that I can't get the values correct either.

The data looks similar to the previous example:

UID	ORDER_ID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	1	A	2010-02-02	Some info 1	Some info 2
002	2	1	A	2010-02-02	Some info 1	Some info 2
003	1	1	B	2010-02-05	Some info 123	Some info 245
004	3	1	A	2010-02-05	Some info ABC	Some info DEF
005	4	2	A	2010-02-05	Some info ABC	Some info DEF

The data comprises orders information for a client and I need to do the same thing as previously, but for the orders.

The output expected is something similar to this:

2010-02-02 2010-02-05

A B A B

Client 1 10 2 13 1

Client 2 20 0 10 0

Client 3 5 1 6 0

Thanks in advance for any help on this.

I've been using a Pivot Table to try and get the proper results, using some set expressions, but nothing came close to the expected results.

[#3]

Hi,

Filter the Status in the Expression itself,

For example:

count({$<Status={'Active'}>} Date)

The above expression will only counts the date which are in active status.

Regards,

Abdul Rahman

[#4]

Hi abdulrahman ,

I already tried that, the problem is that I only have the differences to the days after the status change has happened, and not the whole set of data to compare to.

[#5]

Hi,

Then Try "Flag Method",

You are arriving the difference/ delta,

If your delta > 0, then alias a field "IsUpdated" , and set it to 1 otherwise 0.

So, based on the "IsUpdated" flag, you try to count the orders.

Hope it helps.

[#6]

Hi,

I already tried something similar and it doesn't work.

I can flag the data, but it will be the same set as using a set expression for the {"=update_date = max(update_date)"}, it will only give me the changes that happened in that day and not the total orders still active for the client on that specific day.

Thanks for the suggestion though.

[Anonymous]

I am a wee bit confused. Let me try and figure out what you want to do...Here's your input data

UID	ORDER_ID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	1	A	2010-02-02	Some info 1	Some info 2
002	2	1	A	2010-02-02	Some info 1	Some info 2
003	1	1	B	2010-02-05	Some info 123	Some info 245
004	3	1	A	2010-02-05	Some info ABC	Some info DEF
005	4	2	A	2010-02-05	Some info ABC	Some info DEF

Would the output based on "just" this above provided data be as follows? I am having a disconnect between your example data and the output you provided.

2010-02-02 2010-02-05

A B A B

Client 1 2 0 1 1

Client 2 0 0 1 0

[#8]

Hi,

The samples were disconnected, yes.

[#9]

The second sample should then be:

2010-02-02 2010-02-05

A B A B

Client 1 2 0 2 1

Client 2 0 0 1 0

Sorry about that.

[Anonymous]

Lol, OK! But did I get it right? Based on just the five rows of data you provided, is the following output what you'd expect?

2010-02-02 2010-02-05

A B A B

Client 1 2 0 1 1

Client 2 0 0 1 0