First time record appears

[keithlawrence]

Hi,

Been struggling with this issue for a while now. I have data from different companies on their suppliers along with when they first used that supplier. I need to show graphs and tables of how many new suppliers were used in a month and who those suppliers where. Companies are able to see all the data and also select individual companies. When a user looks at all the data, they need to see the first time that supplier was used, regardless of the company who used it. So a min of the month.

Issue I have, I can't get any aggr or count formula to work and give me the correct number. When I do a basic count, I expect to see March 2017 showing 120. Everything I do gives me 164 and then changes to 225.

Attached is the data I'm working with but I've changed the names to protect my data. Any help would be great!

I've been using this formula but it's not right:

Count({<[Flag Supplier Used]={1}>}Aggr(Min([Supplier MonthYear]),[Supplier Name]))

Thanks

Keith

[sunny_talwar]

Are you trying this in a text box object or a chart? What is Flag Supplier Used flag for? This might look unrelated, but are you using QV12 or above or QV11.2?

[sunny_talwar]

May be create a flag in the script to handle this

Table:

LOAD [Supplier Name],

    Company,

    Date([Supplier MonthYear]) as [Supplier MonthYear],

    [Flag Supplier Used]

FROM

[..\..\Downloads\Supplier Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  If([Supplier Name] = Previous([Supplier Name]), 0, 1) as First_Used_Flag

Resident Table

Order By [Supplier Name], [Supplier MonthYear];

DROP Table Table;

and then this

=Count({<First_Used_Flag = {1}>}[Supplier Name])

[keithlawrence]

I'm using 11.2. Can't upgrade as we have clients using IE8 which will not work with v12.

I can't put this in the script as I need users to be able to select a company and it updates the results. The way you are suggesting would hard code the first time that supplier was used.

For example:

A supplier may have been first used in December 2016 but when they select Company 1 that changes to February 2017 as that company hasn't used them yet.

The flag I put on is there as there are some suppliers that are on the books of the other companies but they have not used them yet. I removed those lines in the sample data.

[sunny_talwar]

Here is another option.... still some manipulation in the script (sorting the date in ascending order), but most of the heavy lifting on the front end....

Script

Table:

LOAD [Supplier Name],

    Company,

    [Supplier MonthYear] as [Supplier MonthYear Temp],

    [Flag Supplier Used]

FROM

[..\..\Downloads\Supplier Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  Date([Supplier MonthYear Temp]) as [Supplier MonthYear]

Resident Table

Order By [Supplier MonthYear Temp];

DROP Table Table;

Expression

=Sum(Aggr(If([Supplier Name] = Above([Supplier Name]), 0, 1), [Supplier Name], [Supplier MonthYear]))

[keithlawrence]

This is giving me a total of 11,195 suppliers but there is only 11,158. It also doesn't allow me to select a month to see the new suppliers for that month.

March 2017 goes from 121 to 225 when I select March 2017. Should remain at the correct number of 120

[sunny_talwar · Accepted solution]

I am seeing 11158, not sure where you are seeing 11,195?

Check this for the selection issue

=Sum(Aggr(If(Only({<[Supplier MonthYear]>}[Supplier Name]) = Above(Only({<[Supplier MonthYear]>}[Supplier Name])), 0, 1), [Supplier Name], [Supplier MonthYear]))

Without selection

With selection

[keithlawrence]

The new formula works perfectly. I'll look into the mis match of numbers. I think that has been caused by something different.

Thanks!