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

[effinty2112]

Hi Gareth,

Try this in a textbox:

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

Regards

Andrew

[Anonymous]

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

[effinty2112]

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

[Anonymous]

Thanks Andrew,

Unfortunately, the result is still the same.

Thanks,

Gareth

[swuehl]

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)

[Anonymous]

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

Thanks anyway

[swuehl · Accepted solution]

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)

[Anonymous]

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

[swuehl]

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.