Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gloria_bertini
Contributor

Get New Customer and uneventful customers

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

1 Solution

Accepted Solutions
gloria_bertini
Contributor

Re: Get New Customer and uneventful customers

I solved the problem using the solution proposed in this thread: set-analysis lost customers

Thanks all for help

16 Replies
mark6505
Valued Contributor III

Re: Get New Customer and uneventful customers

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
Honored Contributor II

Re: Get New Customer and uneventful customers

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

gloria_bertini
Contributor

Re: Get New Customer and uneventful customers

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
Contributor

Re: Get New Customer and uneventful customers

Yes, The choose is dynamic

mark6505
Valued Contributor III

Re: Get New Customer and uneventful customers

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
Honored Contributor II

Re: Get New Customer and uneventful customers

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
Contributor

Re: Get New Customer and uneventful customers

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

agigliotti
Honored Contributor II

Re: Get New Customer and uneventful customers

could you post a screenshot to see the error ?

gloria_bertini
Contributor

Re: Get New Customer and uneventful customers

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

Immagine.png

Community Browser