Get New Customer and uneventful customers

[gloria_bertini]

Good morning everybody,

First of all sorry for my English.

I have a problem to find  new customer for a season and uneventful customers for a season.

New customer are customer that have do orders in this season but non in the previous two while uneventful customers are customers that do orders in previous season but not in the season in exam.

The idea is to create a table in script with all the customer and the season in where a customer do an order and than see if a customer have done order in one season but not in previous.

I do this table:

STAGIONI_DI_ACQUISTO:

LOAD Distinct

CodeCustomer,

CodeSeason;

SQL SELECT *

FROM …;  //This table is taken from the file of orders

left Join

LOAD

Code Season,

IndexSeason,  //a field that give sequence of season

// ApplyMap('MAPPING_STAGIONI',IndexSeason -1,'MANCA DESCRIZONE') as CodiceStagionePrec1,

// ApplyMap('MAPPING_STAGIONI', IndexSeason -2,'MANCA DESCRIZONE') as CodiceStagioneLike1;

SQL SELECT *

FROM ……; // This table is taken from the file of season

Now I don’t know how to procced to count new customer.

I tried lot of different way but nothings works.

Maybe it’s also possible with set analysis, but I don’t know how do this.

Please help me

Thanks in advice

[Mark_Little]

Hi,

You could do in both the script and Set analysis.

Create flags for each season

So

Left join

LOAD

CodeCustomer,

1           as ThisSeasonFlag

RESIDENT STAGIONI_DI_ACQUISTO

WHERE CodeSeason = 'This Season'  //What ever the code for this is

once you have a flag for each season just use set analysis like

COUNT({<ThisSeasonFlag={1},LastSeasonFlag-={1}>}CodeCustomer)

and so on.

You code always even build on the initial flags in script to mark the the New customer and eneventful customers

Mark

[agigliotti]

what you asking for should works based on the season currently selected ? I mean the current season is being choosen dynamically ?

[gloria_bertini]

Hi Mark,

thanks a lot for answer,

the problem is that i have to do this for all the season beacause customer could select season of interest.

How can i do that ?

[gloria_bertini]

Yes, The choose is dynamic

[Mark_Little]

Hi.

There will be a way of doing it, Might need to know a little more about the data to give an exact answer, but i would look to create a new table in the data.

This will have to link to back on Customer, season and you need to create a flag, to see if they ordered in this season.

Then you could use the Previous or Peek function to check if they order last season. Making sure it is ordered by customer and season, somthing like

IF(CodeCustomer=Peek('Codecustomer',-1),

     PEEK('OrderedFlag',-1)

   )          AS OrderLastSeason.

Then each line of data will have a flag for order this season and last season, so will be dynamic to your season selections.

Mark

[agigliotti]

let's try using the below expressions:

//for new customers:

=count( {< CodeCustomer = P( {< IndexSeason = {"$(=Concat( distinct IndexSeason,','))"} >} ) * E( {< IndexSeason = {">=$(=Concat( distinct IndexSeason,',')-2)<=$(=Concat( distinct IndexSeason,',')-1)"} >} ) >} distinct CodeCustomer )

//for  uneventful customers:

=count( {< CodeCustomer = P( {< IndexSeason = {"$(=Concat( distinct IndexSeason,',')-1)"} >} ) * E( {< IndexSeason = {"$(=Concat( distinct IndexSeason,','))"} >} ) >} distinct CodeCustomer )

in this way the user can select the current season from a listbox.

[gloria_bertini]

Thanks very much for answer but, there is problem, qlik don't recognize p() as a report function

[agigliotti]

could you post a screenshot to see the error ?

[gloria_bertini]

Hi Here's a Screenshot, it's seems to be ok but, the P() have not the color of a key word.

CodiceCliente1 is CodeCustomer,

CodiceStagione1 is CodeSeason,

IndiceStagione1 is IndexSeason