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 

may be this 

aggr(
rangesum(above(count(Name),0,rowno())),Year,Month)

Regards 

[Haarika]

Hi, thank you for your response.

this expression simply adds up the above values without removing the duplicates.

Could you please advise on how we can display the cumulative count of unique names per month.

Many thanks again !

[brunobertels]

Hi 

try with distinct : 

aggr(
rangesum(above(count( distinct Name),0,rowno())),Year,Month)

[Haarika]

Hello,

This doesnt work either ! any other approach ?

Thanks !

[brunobertels]

Hi 

can you provide a sample data as the exemple above but where duplicate name are present

and explain the desired output 

for exemple Jan = count ABC + DEF etc 

[Haarika]

Hello,

sample data:

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

Desired output:

YTD Jan - 2 (ABC + DEF)

YTD Feb - 4 (ABC + DEF + GHI + JKL) Note : ABC in Feb data needs to be omitted since it has been counted in Jan

YTD Mar - 4 (ABC + DEF + GHI + JKL) Note : ABC, DEF, GHI in Mar data needs to be omitted since it has been counted in Jan, Feb

Hope this helps ! 

Thank you

[brunobertels · Accepted solution]

Hi 

OK i think in that case rangesum(above( is not the good approach in your case 

here a scrit to test : 

[Table]:

load
*,

num(month(date#([Month],'MMM'))) as Num_Month;
LOAD * INLINE
[
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
](delimiter is ',');

final:
load
distinct Num_Month as AsofMonth,
iterno() as Num_Month
resident Table
while iterno() <=Num_Month;

straight table 

crosstable :

see this post also for the asoftable approach :

https://community.qlik.com/t5/QlikView-App-Dev/Accumulated-Distinct-Count/td-p/1166532 

hope it helps 

[Haarika]

fantastic... this solution works... thanks a ton for your help 🙂

[Haarika]

Hello @brunobertels ,

This is to let you know that, using the AsofTable concept I was able to derive the cumulative distinct count of customers per month. Further to this, I am asked to display the cumulative distinct count of customers per number of interactions per month as given below

The issue being faced is with the customers (D, H & M) who have different number of interactions each month as shown below. Please note: number of interactions is precalculated in the data model

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

Desired output should be 

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	5
G	2021 Mar	1
H	2021 Mar	2
J	2021 Mar	2
K	2021 Mar	1
M	2021 Mar	3
N	2021 Mar	1
O	2021 Mar	2
P	2021 Mar	1

could you please advice a solution for this requirement?

Many thanks,

Haarika