topic Show Line Delta between Periods in QlikView

Show Line Delta between Periods

Mon, 12 Jun 2017 22:55:48 GMT

I am trying to build a table that shows the delta of "Line Counts" between two periods.

My data is similar to this:

Account Month Line_Count

ABC Jan 1

ABC Feb 1

ABC Mar 1

ABC Apr 2

ABC May 2

DEF Jan 2

DEF Feb 2

DEF Mar 2

DEF Apr 2

DEF May 2

HIJ Jan 6

HIJ Feb 5

HIJ Mar 4

HIJ Apr 3

HIJ May 3

KLM Jan 1

KLM Feb 2

KLM Mar 3

KLM Apr 4

KLM May 5

I am trying to build a table that looks like this:

Mar

1 2 3 4 5 6

1 1 0 1 0 0 0

2 0 1 0 0 0 0

Jan 3 0 0 0 0 0 0

4 0 0 0 0 0 0

5 0 0 0 0 0 0

6 0 0 0 1 0 0

So, across the top are the "Line_Counts" for March, and on the left are the "Line_Counts" for Jan

Then, in the table is a count(Account).

The idea here is that we know that between Jan and Mar, one account went from 6 lines to 4 lines (as an example).

If anyone has any insight or thoughts on how this can be accomplished, it would be greatly appreciated.

Thanks in advance,

Todd

Re: Show Line Delta between Periods

kamal_sanguri — Tue, 13 Jun 2017 05:01:23 GMT

Could you elaborate it a bit more? What does the highlighted 1 depicts in table.

Looking at the raw data, I could not figure it out. In Jan we have 2 accounts with 1 Line

Mar

1 2 3 4 5 6

1 1 0 1 0 0 0

2 0 1 0 0 0 0

Jan 3 0 0 0 0 0 0

4 0 0 0 0 0 0

5 0 0 0 0 0 0

6 0 0 0 1 0 0

Re: Show Line Delta between Periods

kamal_sanguri — Tue, 13 Jun 2017 05:02:42 GMT

Does you sample raw data which you have posted talks with the output table you provided?

Re: Show Line Delta between Periods

Tue, 13 Jun 2017 12:27:49 GMT

The raw data does line up with the table. The 1 that you highlighted in red shows that 1 account had 1 line in Jan and also had 1 line in Mar (the other account that had 1 line in Jan had 3 lines in Mar and is counted there... highlighted in blue below):

Mar

1 2 3 4 5 6

1 1 0 1 0 0 0

2 0 1 0 0 0 0

Jan 3 0 0 0 0 0 0

4 0 0 0 0 0 0

5 0 0 0 0 0 0

6 0 0 0 1 0 0