Selected Month IDs matching with Subsequent month

[rob_vander]

Hi All,

Hi All,

I have below set of data

ID	Month	Category
1	Mar-24	High
2	Mar-24	High
3	Mar-24	Medium
4	Mar-24	Low
5	Mar-24	Low
6	Mar-24	Low
7	Mar-24	Medium
1	Apr-24	High
3	Apr-24	Low
4	Apr-24	Medium
5	Apr-24	Medium
6	Apr-24	High
1	May-24	High
2	May-24	Medium
3	May-24	Medium
4	May-24	Low
5	May-24	Low
7	May-24	Low

I have requirement where user selects any month, let's say Mar-24 in this case. All the IDs of Mar-24 need to be matched for next subsequent month, in this case Apr-24 & May-24. Then pick up the corresponding category & count the matched IDs for that category. Assume that user has selected Mar-24 month then I want the output like below

	Apr-24			May-24
	High	Medium	Low	High	Medium	Low
High	1	0	0	1	1
Medium	0	0	1	0	1	1
Low	1	2	0	0	0	2

For eg. In above output highlighted number is calculated with below logic.

For Mar-24 CAtegory Low we have 3 IDs(4,5,6). If we match these IDs in Apr-24, we can see that two IDs became Medium (ID =4,5) and one ID became High( ID =6). Hence Low to Medium count id 2. Likewise all other Category Counts needs to be calculated

Note: I want calculation using chart expression with little bit script solution should be fine.

@MarcoWedel 

[Kushal_Chawda · Accepted solution]

One solution will be to create inline month table to select Month as filter because you won't be able to display subsequent month in chart with selection on same field.

Data:
Load ID, Category, date(Date#(Month,'MMM-YY'),'MMM-YY') as Month;
Load * Inline [
ID Month Category
1 Mar-24 High
2 Mar-24 High
3 Mar-24 Medium
4 Mar-24 Low
5 Mar-24 Low
6 Mar-24 Low
7 Mar-24 Medium
1 Apr-24 High
3 Apr-24 Low
4 Apr-24 Medium
5 Apr-24 Medium
6 Apr-24 High
1 May-24 High
2 May-24 Medium
3 May-24 Medium
4 May-24 Low
5 May-24 Low
7 May-24 Low ](delimiter is '\t');

Month:
LOAD date(FieldValue('Month',RecNo()),'MMM-YY') as select_month
AutoGenerate FieldValueCount('Month');

Create a Pivot table with below two Column Dimension

1) =aggr(only({<Month={">$(=max(select_month))"}>}Month),Month)

2) Category

Create below Measures for each Category

1) High: Count(DISTINCT{<ID=p({1<Month={"$(=date(max(select_month),'MMM-YY'))"},Category={'High'}>})>} ID)

2) Medium: Count(DISTINCT{<ID=p({1<Month={"$(=date(max(select_month),'MMM-YY'))"},Category={'Medium'}>})>} ID)

3) Low: Count(DISTINCT{<ID=p({1<Month={"$(=date(max(select_month),'MMM-YY'))"},Category={'Low'}>})>} ID)

Move Measure Values to Row Dimension of Pivot table