topic Cumulative count for chart in QlikView

Cumulative count for chart

Fri, 13 Dec 2013 07:11:28 GMT

Hi, I have a question about count a dimension.

Here is the sample :

ID	Date
A	1-Jan
B	1-Jan
C	1-Jan
B	2-Jan
C	2-Jan
C	2-Jan
D	2-Jan
C	3-Jan
E	3-Jan

There are 5 IDs : A,B,C,D,E

I want to make chart that show :

1 Jan has 3 IDs ( That 3 IDs from count distinct 1 Jan : A, B C)

2 Jan has 4 IDs ( That 4 IDs from count distinct 1 Jan to 2 Jan : A, B C,D)

3 Jan has 5 IDs ( That 5 IDs from count distinct 1 Jan to 3 Jan : A, B C,D,E)

Does someone has any idea what kind of expression I could use for this case?

Thanks.

Re: Cumulative count for chart

Fri, 13 Dec 2013 07:45:29 GMT

in table with peek()

COUNT =aggr(count(ID),Date)

order table by aggr(count(ID),Date)

PROG = peek(PROG)+COUNT maybe...

in script

table1:

load count(ID) as K, Date resident table group by Date

table2:

load numsum( keep(PROG) ,K) as PROG, * resident Tabella

Re: Cumulative count for chart

Fri, 13 Dec 2013 08:27:29 GMT

ok sorry:

TABLE:

LOAD * INLINE [

ID, Data

La, 1-gen

B, 1-gen

C, 1-gen

B, 2-gen

C, 2-gen

D, 2-gen

C, 3-gen

E, 3-gen

];

DATE:

LOAD Distinct Data as CALENDAR Resident TABLE Order BY Data;

FOR i = 1 to NoOfFields('DATE')

LET D = Peek('DATE',$(i),'Data');

load ID as ID1, Data Resident TABLE WHERE NOT Exists(ID1,ID);

NEXT i

Re: Cumulative count for chart

Fri, 13 Dec 2013 08:47:19 GMT

chart type: BAR CHART

dimension: Data

Count(DISTINCT ID1) with full accumulation

Re: Cumulative count for chart

maxgro — Fri, 13 Dec 2013 08:47:29 GMT

Tab:

LOAD * INLINE [

ID, Date

A, 1-Jan

B, 1-Jan

C, 1-Jan

B, 2-Jan

C, 2-Jan

D, 2-Jan

C, 3-Jan

E, 3-Jan

];

// new Date2 for chart dimension

Date:

LOAD Distinct Date as Date2 Resident Tab;

Chart

Dimension Date2

Expression count(distinct if(Date<=Date2, ID))

Re: Cumulative count for chart

Fri, 13 Dec 2013 08:52:00 GMT

così funziona solo se ID non è in nessuna altra tabella della nuvola però.. o sbaglio?

ho sbattuto un sacco di volte la testa su questo tipo di richiesta e mi sono sempre piegato a fare il calcolo sullo script per non impazzire con aggr e balle varie che in 2 passaggi di script risolvo

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:27:32 GMT

if we use full accumulation, it will give us

1 jan has 3 IDs

2 jan has 6 IDs

3 jan has 8 IDs

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:29:54 GMT

thanks a lot, this is what I meant

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:30:19 GMT

Hi Paulus,

take a bar or line and use Date as dimension and count(distinct ID) as expression

and tick cumulative total

best regards

Chris

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:30:44 GMT

From where "DATE" field come?

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:30:58 GMT

no...

in script:

DATE:

LOAD Distinct Data as CALENDAR Resident TABLE Order BY Data;

FOR i = 1 to NoOfFields('DATE')

LET D = Peek('DATE',$(i),'Data');

load ID as ID1, Data Resident TABLE WHERE NOT Exists(ID1,ID);

NEXT i

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:32:29 GMT

if we use full accumulation, it will give us

1 jan has 3 IDs

2 jan has 6 IDs

3 jan has 8 IDs

Re: Cumulative count for chart

Fri, 13 Dec 2013 09:33:20 GMT

DATE is a table:

DATE:

LOAD Distinct Data as CALENDAR Resident TABLE Order BY Data;

Re: Cumulative count for chart

maxgro — Fri, 13 Dec 2013 09:33:22 GMT

Credo funzioni anche con altri ID nel modello

I possibili problemi potrebbero essere che la Date2 isola non sente le selezioni sulla Date (a meno di .....), che l'espressione con l'if è più "pesante" nei calcoli.

Altra soluzione potrebbe essere forse l'AsOf, si trova sul forum

Ora guardo il tuo metodo.

Sì è una di quelle cose che periodicamente tornano e che dovrebbero essere banali ma in Qv non tanto.