topic Re: Count Supplier Numbers based on Invoice Date against Selections in QlikView

Count Supplier Numbers based on Invoice Date against Selections

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

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

Re: Count Supplier Numbers based on Invoice Date against Selections

effinty2112 — Mon, 18 Sep 2017 13:25:25 GMT

Hi Gareth,

Try this in a textbox:

=Count(aggr(if(min([Invoice Date]) = min({$<Year=,Month=>}[Invoice Date]),SupplierID),SupplierID))

Regards

Andrew

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Mon, 18 Sep 2017 13:36:13 GMT

Hi Andrew,

Nope it doesn't quite work. It's a shame my app and data is stored on my server otherwise I could share it with you all and it would make it easier for you guys when I ask for help!

Running a manual check by listing every supplier number in one column and their Min Invoice Date, there should be 14 in Aug 2017, however if I select Aug 2017 in the relevant list boxes, it returns 42 as the result of the expression.

Thanks,

Gareth

Re: Count Supplier Numbers based on Invoice Date against Selections

effinty2112 — Mon, 18 Sep 2017 13:40:57 GMT

Hi Gareth,

Try it again with the DISTINCT keyword:

=Count(DISTINCT aggr(if(min([Invoice Date]) = min({$<Year=,Month=>}[Invoice Date]),SupplierID),SupplierID))

good luck

Andrew

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Mon, 18 Sep 2017 13:45:53 GMT

Thanks Andrew,

Unfortunately, the result is still the same.

Thanks,

Gareth

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Mon, 18 Sep 2017 13:56:18 GMT

I would create a new field in the LOAD script that stores for every supplier the minimum invoice date, something like

SupplierTable:

LOAD

SupplierID,

...

FROM ...;

InvoiceTable:

LOAD

SupplierID,

InvoiceDate,

...

FROM ...;

LEFT JOIN (SupplierTable)

LOAD SupplierID,

MonthName(Min (InvoiceDate)) as [Min Invoice Date]

RESIDENT InvoiceTable

GROUP BY SupplierID;

Then just make your selection in Min Invoice Date field and Count(DISTINCT SupplierID)

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Mon, 18 Sep 2017 14:19:13 GMT

Unfortunately I cannot load script, we are barred by our administrator.

Thanks anyway

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Mon, 18 Sep 2017 14:26:56 GMT

Ok, then you can create a list box with a field expression (select <expression> from the field drop down on general tab):

=Aggr( MonthName( Min({1} InvoiceDate)), Supplier)

Select a value from the list box and count(distinct Supplier)

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Tue, 19 Sep 2017 08:35:25 GMT

Hi guys

Something like this should work, whereby counting all the distinct supplier numbers with a "1", however my expression has an error. Any ideas where this is going wrong?

=Count({<If(Year(Min( {1} [Invoice Date])) = GetFieldSelections(Year) And (Month(Min( {1} [Invoice Date])) = GetFieldSelections(Month)), 1, 0) >}Distinct [Supplier Number])

Many thanks,

Gareth

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Tue, 19 Sep 2017 09:51:18 GMT

This won't work because you can only use field names left of the equal sign in a set analysis field selection modifier. you can't use expressions like Year(Min(Date)) here, and you can't just use GetFieldSelections() on the right side.

If you want to use set analysis, I would suggest creating the field in the data model as indicated above.

There are other alternatives using set analysis, but it's crucial to know your data model and data if you need help here, since we need to know how your current selections may influence the record set. Also Qlik is a little picky when it comes to making date field selection modifications in set analysis. Format of the date field may be important.

So it is helpful if you can create and upload a small sample QVW with some mock up data.

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Tue, 19 Sep 2017 10:24:36 GMT

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

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Tue, 19 Sep 2017 10:57:45 GMT

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

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Tue, 19 Sep 2017 11:01:18 GMT

And using set analysis:

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

Re: Count Supplier Numbers based on Invoice Date against Selections

Colin-Albert — Tue, 19 Sep 2017 12:33:38 GMT

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

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Thu, 21 Sep 2017 10:59:19 GMT

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

Re: Count Supplier Numbers based on Invoice Date against Selections

Anonymous — Tue, 26 Sep 2017 13:24:05 GMT

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

Re: Count Supplier Numbers based on Invoice Date against Selections

swuehl — Tue, 26 Sep 2017 15:40:32 GMT

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