topic Cumulative count of distinct values in a pivot table in App Development

Cumulative count of distinct values in a pivot table

Haarika — Wed, 02 Feb 2022 14:05:03 GMT

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 :

Number

Jan YTD

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2021

2020

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

Many thanks !

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Thu, 27 Jan 2022 09:53:46 GMT

may be this

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

Regards

Re: Cumulative sum of distinct values in a pivot table

Haarika — Mon, 31 Jan 2022 16:18:31 GMT

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 !

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Mon, 31 Jan 2022 16:45:29 GMT

try with distinct :

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

Re: Cumulative sum of distinct values in a pivot table

Haarika — Mon, 31 Jan 2022 17:09:04 GMT

Hello,

This doesnt work either ! any other approach ?

Thanks !

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Mon, 31 Jan 2022 17:38:06 GMT

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

Re: Cumulative sum of distinct values in a pivot table

Haarika — Mon, 31 Jan 2022 17:57:05 GMT

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

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Tue, 01 Feb 2022 11:22:27 GMT

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

Re: Cumulative sum of distinct values in a pivot table

Haarika — Wed, 02 Feb 2022 14:04:15 GMT

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

Re: Cumulative sum of distinct values in a pivot table

Haarika — Thu, 17 Feb 2022 03:30:30 GMT

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

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Thu, 17 Feb 2022 10:47:42 GMT

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

Re: Cumulative sum of distinct values in a pivot table

Haarika — Fri, 18 Feb 2022 02:36:05 GMT

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

Re: Cumulative sum of distinct values in a pivot table

brunobertels — Fri, 18 Feb 2022 11:35:28 GMT

I'am afraid your requierment is beyond my knowledges.

Nether the less try this :

based on my last inline load testing above :

[Table]:
load
*,

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