Qlikview Matrix table. Calulating Rating movement

[davech]

Hi All,

I am new to qlikview, hence please pardon me if this is rather straightforward.

I have a requirement to count the number of customer that falls under a certain rating over the respective month. the objective is to measure how many customer got improved or worst off rating across the months.

To keep it simple, i have a dataset with 3 columns; [CUSTOMER], [MONTHYEAR], [RATING]. below is a sample:

CUSTOMER	MONTHYEAR	RATING
CUSTOMER A	Mar-21	10
CUSTOMER A	Apr-21	10
CUSTOMER A	May-21	10
CUSTOMER A	Jun-21	10
CUSTOMER A	Jul-21	10
CUSTOMER A	Aug-21	10
CUSTOMER A	Sep-21	10
CUSTOMER A	Oct-21	10
CUSTOMER A	Nov-21	10
CUSTOMER A	Dec-21	9
CUSTOMER A	Jan-22	9
CUSTOMER A	Feb-22	9
CUSTOMER A	Mar-22	8
CUSTOMER B	Mar-21	5
CUSTOMER B	Apr-21	5
CUSTOMER B	May-21	5
CUSTOMER B	Jun-21	5
CUSTOMER B	Jul-21	6
CUSTOMER B	Aug-21	6
CUSTOMER B	Sep-21	6
CUSTOMER B	Oct-21	6
CUSTOMER B	Nov-21	6
CUSTOMER B	Dec-21	6
CUSTOMER B	Jan-22	6
CUSTOMER B	Feb-22	7
CUSTOMER B	Mar-22	7

The expected output should be as such (if the user selected May-21 and Feb-22):

May-21	Feb-22	1	2	3	4	5	6	7	8	9	10
1
2
3
4
5								1
6
7
8
9
10										1

from the above table, it should tell the user that there is a customer has a rating of 5 in May-21 and rating of 7 in Feb-22, and that a customer with rating of 10 in May-21 and rating of 9 in Feb-22.

Can any experts direct me on how to accomplish this in Qlikview please?

[Akeem]

Much appreciated so a phenomenal plan, your thought worked for me.

H and R Block

[smith11484]

Add a master calendar with a YTD function. Then set default clear state to always resolve to YTD. You can still look at any month then etc. myccpay account

[davech]

Thanks all for the reply.

I've managed to create the following:

Feb-22	May-21	5	7
5		1	-
7		-	1

however, im expecting it to be like this:

Feb-22	May-21	5
7		1

Dimension used:

Expression:

DIMENSION:

MONTHYEAR

RATING

MEASURES:

=count(distinct CUSTOMER)

can any one please advise how can i get to the 2nd table please?

[Schaefer]

chart, a range function can calculate a single value from an inter-record array. In the load script, a range function can calculate a single value from an array of values in an internal table.

Mylincoln Portal