Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

Hi all,

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

UIDCLIENT_IDSTATUSUPDATE_DATEINFO1INFO2
0011A2010-02-02Some info 1Some info 2
0022A2010-02-02Some info 1Some info 2
0031B2010-02-05Some info 123Some info 245
0043A2010-02-05Some info ABCSome 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 -

14 Replies
Highlighted
Not applicable

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

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:

UIDORDER_IDCLIENT_IDSTATUSUPDATE_DATEINFO1INFO2
00111A2010-02-02Some info 1Some info 2
00221A2010-02-02Some info 1Some info 2
00311B2010-02-05Some info 123Some info 245
00431A2010-02-05Some info ABCSome info DEF
00542A2010-02-05Some info ABCSome 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.

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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.

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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.

Highlighted
Creator II
Creator II

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

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

UIDORDER_IDCLIENT_IDSTATUSUPDATE_DATEINFO1INFO2
00111A2010-02-02Some info 1Some info 2
00221A2010-02-02Some info 1Some info 2
00311B2010-02-05Some info 123Some info 245
00431A2010-02-05Some info ABCSome info DEF
00542A2010-02-05Some info ABCSome 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

Highlighted
Not applicable

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

Hi,

The samples were disconnected, yes.

Highlighted
Not applicable

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

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.



Highlighted
Creator II
Creator II

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

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