count a field using another field distinct

[#1]

Hi,

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

regards,

MT

[swuehl]

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?

[#3]

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,

MT

[swuehl]

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

[#5]

Hi,

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

regards,

MT

[#6]

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,

MT

[swuehl]

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))

[#8]

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,

MT

[swuehl]

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

[#10]

It always returns 0. I really dont understand it.