Expression Help

[Anonymous]

Hi guys,

I have two fields, Supplier Number and Min Invoice Date.

How would I write an expression that counts all Supplier Numbers whereby the Min Invoice Date value falls within the previous month;

For example a Supplier Number has a Min Invoice Date of 12/08/2017 so I want this to be counted as it falls between 01/08/2017 and 31/08/2017

Many thanks,

Gareth

[vishsaggi]

May be this?

= Count({< SupplierNum = {"$(= '>=' & MonthStart(Min(InvoiceDate)) & '<=' MonthEnd(Min(InvoiceDate)))"} >} SupplierNum)

Or Create a Resident table to get MinInvoice data for those suppliersname like

LOAD SupplierName,

           Min(InvoiceDate) AS MinInvoiceDate

Resident YourActualTable

Group By SupplierName;

Then use this new Field in your set analysis. Like

= Count(DISTINCT {< SupplierNum = {"$(= '>=' & MonthStart(MinInvoiceDate) & '<=' & MonthEnd(MinInvoiceDate))"} >} SupplierNum)

[vishsaggi]

Sorry change SupplierName to SupplierNumber.

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {<[Supplier Number] = {"=Min([Invoice Date]) >= MakeDate(2017, 8, 1) and Min([Invoice Date]) <= MakeDate(2017, 8, 31)"}>}[Supplier Number])

[Anonymous]

Bingo, that worked a treat thank you.

[Anonymous]

That didn't work for me unfortunately. Thanks for responding though.

[Anonymous]

Hi,

How would I amend this so that it only shows suppliers that have their min invoice date within the month of selection (using a list box)?

Thanks,

Gareth

[sunny_talwar]

Not sure I understand your data model well enough to answer this... would you be able to share a small sample?

[Anonymous]

I cannot share the app or the background data because it's stored on a server unfortunately.

However, the below screen shot shows the selections that I want to determine the result in the New Suppliers expression.

Basically, it's currently set as default to show the number used in the previous month based on today's date (so August 2017). I wish to make it so that the number of new suppliers shows the number used in a relevant month (so for example, 2nd January 2017) that weren't used prior.

The two fields that contains the data to base this on are [Supplier Number] and [Invoice Date]

Many thanks,

Gareth