Cumulative count of distinct values in a pivot table

[Haarika]

Hello,

I have a requirement to display the Year-to-date sum of unique customers per month and year in a pivot table.

Sample data is provided below:

Year	Month	Name
2021	Jan	ABC
2021	Jan	DEF
2021	Feb	GHI
2021	Feb	JKL
2021	Feb	ABC
2021	Mar	ABC
2021	Mar	DEF
2021	Mar	GHI

Required format : 

P1

P2

Number

Jan YTD

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2021

2

4

4

2020

Could you please advice on how to achieve this request. @sunny_talwar , @Kushal_Chawda 

Many thanks !

[brunobertels]

Hi 

Try this 

[Table]:
load
*,

num(month(date#([Num_Month],'YYYY MMM'))) as Num_Month1;
LOAD * INLINE
[
Name,Num_Month,No. of Interactions
A,2021 Jan,2
D,2021 Feb,2
H,2021 Feb,1
I,2021 Feb,1
L,2021 Feb,1
M,2021 Feb,1
B,2021 Mar,1
C,2021 Mar,1
D,2021 Mar,3
G,2021 Mar,1
H,2021 Mar,1
J,2021 Mar,2
K,2021 Mar,1
M,2021 Mar,2
N,2021 Mar,1
O,2021 Mar,2
P,2021 Mar,1
](delimiter is ',');

resulting table : 

Use Name and  Num_Month as Dimensions 

then as mesure : 

aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1)

(Where Num_Month1 = num(month(date#([Num_Month],'YYYY MMM'))) as Num_Month1 added in script as a preceding load statement in my case 

Regards 

[Haarika]

Thank you @brunobertels for sharing this solution.

In addition to the above, I assigned frequency values i.e.1,2,3,4, >=5 based on the cumulative interactions per account per month. Please refer to sample data set. 

Account Name	Num_Month	Frequency
A	2021 Jan	2
D	2021 Feb	2
H	2021 Feb	1
B	2021 Feb	1
C	2021 Feb	1
M	2021 Feb	1
E	2021 Mar	1
F	2021 Mar	1
D	2021 Mar	>= 5
G	2021 Mar	1
H	2021 Mar	2
I	2021 Mar	2
J	2021 Mar	1
M	2021 Mar	3
K	2021 Mar	1
L	2021 Mar	2
N	2021 Mar	1

Now the requirement is to display the cumulative distinct count of accounts per frequency per month as shown below. 

	Jan	Feb	Mar	Apr
1	0	4	8
2	1	2	4
3	0	0	1
4	0	0	0
>=5	0	0	1

As per the sample data, Accounts D, H & M are available in both Feb and Mar. While using the Asoftable accumulation method, in March Account D is counted twice i.e in Freq 2 (as in Feb) and Freq >=5 (as in Mar). LIkewise for H & M too. Therefore, I need a solution that would display the cumulative distinct count of accounts based on their latest frequency for each month?

Many thanks,

Haarika 

[brunobertels]

Hi 

I'am afraid your requierment is beyond my knowledges. 

Nether the less try this : 

based on my last inline load testing above :

[Table]:
load
*,

num(month(date#([Num_Month],'YYYY MMM'))) as Num_Month1;
LOAD * INLINE
[
Name,Num_Month,No. of Interactions
A,2021 Jan,2
D,2021 Feb,2
H,2021 Feb,1
I,2021 Feb,1
L,2021 Feb,1
M,2021 Feb,1
B,2021 Mar,1
C,2021 Mar,1
D,2021 Mar,3
G,2021 Mar,1
H,2021 Mar,1
J,2021 Mar,2
K,2021 Mar,1
M,2021 Mar,2
N,2021 Mar,1
O,2021 Mar,2
P,2021 Mar,1
](delimiter is ',');

I created a cross table with : 

Frequency Line dimension like this : 

=aggr(if(
max(aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1))=1,
1,
if(
max(aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1))=2,
2,
if(
max(aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1))=3,
3,
if(
max(aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1))=4,
4,
if(
max(aggr(rangesum(above(sum([No. of Interactions]),0,rowno())),Name,Num_Month1))>=5,
'>5'))))),Num_Month,Name)

Month column dimension with Num_Month field 

Then as mesure 

count(Name) 

resulting Table : 

It seems to work without selection , but when you select a Name ( M for example ) i have this : 

So not sure it is the behaviour you want. 

Sorry not to be able to help you more for this 

Regards