Count Supplier Numbers based on Invoice Date against Selections

[Anonymous]

Hi

I'm trying to write an expression that will count distinct Supplier Numbers based on their min invoice date against the year and months selected.

For example, I have 2 out of 100 supplier number records with a min invoice date of Jun 2014. When I change my selections in the Month and Year list boxes to Jun and 2014, I want 2 to be returned.

Any ideas how I write this?

Someone has kindly helped me work out how find the min Invoice Date for each supplier number (without it changing when I change selections) - Month(Min( {1} [Invoice Date])) & Year(Min( {1} [Invoice Date]))

Many thanks in advance,

Gareth

[Anonymous]

Ok dummy file attached.

So I'm looking for a textbox that will give the number of suppliers used within any selection made on the year and month list boxes using the min value of their invoice date.

Thanks for your help,

Gareth

[swuehl]

An expression using advanced aggregation could look like

=Count( DISTINCT Aggr( If( Year(Min({1} [Invoice Date]))=Year and Month(Min({1} [Invoice Date]))=Month, [Supplier Number]), [Supplier Number]))

[swuehl]

And using set analysis:

=Count({<[Supplier Number] = {"=Year(Min({1} [Invoice Date]))=Year and Month(Min({1} [Invoice Date]))=Month"}>}DISTINCT [Supplier Number])

[Colin-Albert]

If you generate your Month & Year fields using date functions so they are held as dual values not text then this expression will work in a text box.

     year([Invoice Date]) as Year,

     month([Invoice Date]) as Month,

=count({1 <Year={"<= $(=max(Year))"}, Month={"<= $(=max(Month))"}>} distinct  [Supplier Number])

[Anonymous]

Hi guys

New dummy file - our administrator will create the variable for us on the server.

So, with this in mind, what expression will be needed in the text box to return the correct value (for current selection (Jan 2015), should be 74)?

Many thanks,

Gareth

[Anonymous]

Hi,

Thanks for your help, I managed to get this suggestion to work in my dummy file.

Is there a way of doing without the additional list box and use the existing Year and Month list boxes already existing?

Thanks,

Gareth

[swuehl]

I've already posted two expressions that work on your unmodified data model, both return 74.