topic First time record appears in QlikView

First time record appears

keithlawrence — Thu, 01 Jun 2017 10:43:27 GMT

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

Re: First time record appears

sunny_talwar — Thu, 01 Jun 2017 11:09:36 GMT

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?

Re: First time record appears

sunny_talwar — Thu, 01 Jun 2017 11:22:12 GMT

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

Re: First time record appears

keithlawrence — Thu, 01 Jun 2017 12:04:47 GMT

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.

Re: First time record appears

sunny_talwar — Thu, 01 Jun 2017 12:12:51 GMT

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

Re: First time record appears

keithlawrence — Thu, 01 Jun 2017 14:43:54 GMT

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

Re: First time record appears

sunny_talwar — Thu, 01 Jun 2017 15:21:01 GMT

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

Re: First time record appears

keithlawrence — Thu, 01 Jun 2017 15:27:18 GMT

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

Thanks!