topic Re: count a field using another field distinct in QlikView

count a field using another field distinct

Wed, 19 Sep 2012 08:11:55 GMT

Hi,

I want to count my bill number on distinct date. how can I do this?

regards,

Re: count a field using another field distinct

swuehl — Wed, 19 Sep 2012 09:26:21 GMT

Not sure if I understand your request properly, but maybe

=count( aggr( [bill number], date))

Or could you post some lines of sample date together with your expected outcome?

Re: count a field using another field distinct

Wed, 19 Sep 2012 09:30:52 GMT

ok my data looks like this,

Date, BillNr, Store, Product, Price

01.01.2011, 6, 730, 124, 120

01.01.2011, 6, 730, 258, 159

05.01.2011, 6, 730, 456, 250

so the count should be 1 for the BillNr 6 on 01.01.2011 in Store 730

and count should be 1 for the BillNr 6 on 05.01.2011 in Store 730

Does this make my problem more understandable?

Thanks for your help

regards,

Re: count a field using another field distinct

swuehl — Wed, 19 Sep 2012 09:38:53 GMT

Ok, the solution will also depend on where / in which context you want to calculate your result.

For example, if you are using a straight table with dimensions, BillNr, Store, Date, an expression like count( distinct BillNr) should be enough.

You can also extend my above suggestion to include store number:

=count( aggr( BillNr, Store, Date))

You can use this expression in a straigt table or in a text box.

Regards,

Stefan

Re: count a field using another field distinct

Wed, 19 Sep 2012 09:55:34 GMT

Hi,

you answer was helpfull but the count is still a little bit too high. I have more Bills then I actually have.

regards,

Re: count a field using another field distinct

Wed, 19 Sep 2012 10:02:44 GMT

ok,

i know why it is too high. I have also a field in my table that has 0 or 1 for maincustomer so how can I do it that i only see the BillNr that was bought form a maincustomer??

regards,

Re: count a field using another field distinct

swuehl — Wed, 19 Sep 2012 10:07:32 GMT

Not sure which approach you are following, but you can use a conditional or a set expression to limit your records to maincustomer, maybe like

=count({<maincustomer = {1}>} aggr( BillNr, Store, Date))

Re: count a field using another field distinct

Wed, 19 Sep 2012 11:22:16 GMT

ok I just don't know how to get the right result. ok I have a straight table and the dimension is store. if I use count(aggr(Date, BillNr)) -> no result (QV cant do it)

count(aggr(BillNr, Date)) -> weird result

count(aggr(store,BillNr, date)) -> better result but still wrong

I dont know what to do anymore! Does anyone have an idea what I'm doing wrong?

regards,

Re: count a field using another field distinct

swuehl — Wed, 19 Sep 2012 11:56:09 GMT

order of arguments to aggr() function is important, first is aggr() expression, then following aggr dimensions.

Since you want to count BillNr, I assume you need

=count(aggr(BillNr, Store, Date))

to count distinct BillNr per Store and Date

Re: count a field using another field distinct

Wed, 19 Sep 2012 11:59:39 GMT

It always returns 0. I really dont understand it.

Re: count a field using another field distinct

swuehl — Wed, 19 Sep 2012 12:06:38 GMT

Check that all field names are spelled correctly, QV is case sensitive with field names.

Could you upload a small sample file?

Re: count a field using another field distinct

Thu, 20 Sep 2012 07:48:04 GMT

Thanks for all you help it is really appriciated but I solved it by creating a new field and counting this field distinct.

regards,