How to use distinct function .

[narender123]

Hi All,

My current expression is :

=Count({<flag={1}>}distinct filer)

This distinct will work for monthly basis as my dimension is month wise.But I want this distinct function based on Yearly basis with the same monthly dimension.How i cant achieve this.

table:

Column      No of Filer

-----------------------------------------

Jan2014               3

Feb2014              4

Mar2014              5

Apr2014               6

-

-

Dec2014            15

Thanks,

Narender

[narender123]

Hi Tresco,

Thanks for the reply.

This time i dont have qlikview.As i am working on server directly.But i have shown the condition in my original query.

table:

Column      No of Filer

-----------------------------------------

Jan2014               3

Feb2014              4

Mar2014              5

Apr2014               6

-

-

Dec2014            15

I am using this:

=Count({<flag={1}>}distinct filer)

Pls advice?

[tresesco]

Sorry, expected output isn't yet crear to me.

[#4]

Hi ,

Please create some sample dummy record set with expected output in excel. It will help us to understand actual requirement .

thanks

[jonathandienst]

Hi

I assume what you want to is count each Filer once. After it has been counted in one month, you no longer want to count it in later months. In other words, you want a count of the first time the Filer exists.

This is going to be tricky in an expression and if your data set is large, it may perform badly. So what I recommend is that a flag is created during load - the first time each Filer is encountered, the flag is set to 1. In later months, the flag is set to 0. Something like this (assuming you load the data in date order):

LOAD ....

     date,

     filer

     If(Exists(filerID), 0, 1) As FirstInstanceFlag,

     ....

And then in your table, the expression becomes simply:

  =Sum({<flag={1}>} FirstInstanceFlag)

HTH

Jonathan

[Peter_Cammaert]

Open the original discussion. Do not view my reply in your Inbox or on your phone. The attachment will only be visible/downloadable in the original discussion.