topic Re: Expression Help in QlikView

Expression Help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression Help

vishsaggi — Wed, 13 Sep 2017 14:49:46 GMT

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)

Re: Expression Help

vishsaggi — Wed, 13 Sep 2017 14:50:23 GMT

Sorry change SupplierName to SupplierNumber.

Re: Expression Help

sunny_talwar — Wed, 13 Sep 2017 14:53:03 GMT

May be this

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

Re: Expression Help

Anonymous — Wed, 13 Sep 2017 15:01:39 GMT

Bingo, that worked a treat thank you.

Re: Expression Help

Anonymous — Wed, 13 Sep 2017 15:02:01 GMT

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

Re: Expression Help

Anonymous — Fri, 15 Sep 2017 09:33:14 GMT

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

Re: Expression Help

sunny_talwar — Fri, 15 Sep 2017 11:16:42 GMT

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

Re: Expression Help

Anonymous — Mon, 18 Sep 2017 08:05:46 GMT

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