Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Count new enteries

Hi

I have a table with 2 columns. Column one is the name of the ceremonies and the second one is for participants of that ceremony.

Now i want to have the number of new persons in each ceremoney.

this is my table:

Person_Code     Ceremony

50                       a

        50                    b

        50                    c

        51                    b

        52                        b

so the result should be like this:

ceremoney    number_New_Participants

a                         1      =(50)

b                         2     =(51,52) (not 50 because it is in a ceremony)

c                         0      (not 50 because it is in a ceremony)

can you please help me making the formula.

thank you very much in advanced.

    

11 Replies
chauhans85
Esteemed Contributor

Count new enteries

how you identify that perticular person is in ceremony or not

?

MVP
MVP

Re: Count new enteries

Hi MK_Komaei,

If I understood you correctly, the temporal order of your Ceremonies is determined by alphabetical order of their names.

If so, I think you could use something like

=count( if(aggr(NODISTINCT MinString(aggr(Ceremony,Ceremony,Person_Code)),Person_Code)=Ceremony,Person_Code))

as chart expression.

Alternatively, you could do similar in the script using a group by LOAD.

See attached sample.

Hope this helps,

Stefan

edit: And if you Ceremonies are ordered by something else, you could probably just add a numerical CeremonyOrder field and use this field and  Min instead of MinString in above expression.

Not applicable

Re: Count new enteries

Thank you for your response,

But in fact it did not help me. as I changed the ceremony from alphabetic into a code as below but the result will be changed incorrectly.

I changed

a=10012

b=102

c=1003

d=100224

Not applicable

Re: Count new enteries

Thank you for your response,

But in fact it did not help me. as I changed the ceremony from alphabetic into a code as below but the result will be changed incorrectly.

I changed

a=10012

b=102

c=1003

d=100224

the file is attached below.

MVP
MVP

Re: Count new enteries

Hi MK_Komaei,

you need to somehow define the order of your Ceremonies, otherwise it's hard to say when to count a Person_Code as new entry.

I assume that in your sample, you define this order by load order.

As I said, all you need to do is adding a column with the Ceremonies order, see attached as how I do this for load order.

If you have more information, e.g. a Ceremony date, you could use this date as order (a recognized date has a numerical representation).

Hope this helps,

Stefan

Not applicable

Re: Count new enteries

Hi there.....check the attached doc....I've changed one small bit in the script.

Not applicable

Re: Count new enteries

Forgot to mention, the qv doc I used is swuehl's....just changed one line in there..

qliksus
Valued Contributor

Count new enteries

Hi,

try this in expression

count(aggr(Person_Code,Person_Code))

IAMDV
Honored Contributor II

Re: Count new enteries

qliksus - Very clever workaround.

Cheers - DV

Community Browser