Ratio Cumulative customer order by month

[random_user_3869]

Hello everyone,

Here is the following:

Table:
Load* Inline [

YEarMonthNum, Customer_Num_Sales, Income
2022-05, 25E, 350€
2022-06,  25E, 176€

2022-07, 26F, 140€

2022-08, 35E, 550€;

I want to create a script that can count the number of customer whose sales is superior to 150€.

But i have an issue is regarding the following:

in my examplen 2022-05 and 2022-06 the cusomer 25E is listed TWICE.

I want to do it for cumulative month.

So a new customer is a customer whose sales during the current period (may 2022) is superior to 150€ but in the last 12 months (not counting current months) the sales of this customer is zero.

I struggle counting only a customer once because i don't want to add duplicate.

Anyone can help ?

THanks 

[Kushal_Chawda]

@random_user_3869  Not sure if I understood it completely, but what does below give? Some more sample data with expected output will be more helpful. Now you can use below expression

=count ({<Customer_Num={"=sum(Sales)>150"}>} distinct Customer_Num)

[random_user_3869]

Sorry i need to explain more.

Basically in qlik i have the following script that calculate for each period the number of customer where sales >150.

But in my script i create a loop for each period but in the UI I need to display for exemple the number total of distinct cutomer for period 1 and 2.

so let's say that a customer pays 200€ on period A and the same pays on period B. I want to count this customer only once which is on period A.

the lines in red = sales inferior to 150 so we don't count

So if i do a count distinct client on all the period (only the green) i get 4.

But the customer 755 appear twice but we only want to count this client only once (at the first period it appear).

So basically count customer = 3.

That what i want to write in script

SO here is my script in qlik

$(vL.TableName):

LOAD DISTINCT
[CUSTOMER NUMBER],
[SALES REVENUES],
[NEW CUSTOMERS],
'[Flag Periode],
SOCIETY_NUMBER

Resident Table;

FOr i = 1 to 23;
Let vL.PeriodeMoisNum = '$(i)'; ==> EQUALS MONTH NUM

Trace Periode : $(vL.PeriodeMoisNum);
//N2
CONCATENATE($(vL.TableName))
LOAD DISTINCT
TRIM(TEXT(subfield(CUSTOMER_NUM,'-',2))) AS [CUSTOMER NUMBER],
SALES_REVENUES AS [SALES REVENUES],
NEW_CUSTOMER_COUNT AS [NEW CUSTOMERS],
'$(i)' AS [Flag Periode],
SOCIETY_NUMBER

;

[random_user_3869]

Anyone has an insight ?

Thank you

[Kushal_Chawda]

@random_user_3869  is there specific reason to use loop? 

[random_user_3869]

Yes because each period calculate the period where the sales is made.

Let's say period = 0

so sales revenu is calculated durring the current period. (we check the sales for the customer)

And to say if it's a new client we check if no sales was made in the previous 12 month

period = 1

so sales revenu is calculated durring the previous period (current period - 1). (we check the sales for the customer)

And to say if it's a new client we check if no sales was made in the previous (12 month minus 1)

[Kushal_Chawda]

@random_user_3869  after loop. you can have additional load to create flag to see sales >150. 

Final:
LOAD *,
     if(SALES>150,1,0) as Flag_sales
resident $(vL.TableName);

drop table $(vL.TableName);

Now, you can use this flag in measure
=count({<Flag_sales={1}>}distinct Customer)

[random_user_3869]

Thanks but with my script (not shown here) i already filter the sutomer whose sale >150.

In my script (not shown) i wrote a case and the output value string is 'new'  AS NUMBER_OF_CUSTOMER

Then in my set analysis i do something like: Count({<Period = {'1','2'}>}NUMBER_OF_CUSTOMER)

By doing that i count twice the customer 755 for example.

ANd i don't want that

[Kushal_Chawda]

@random_user_3869  Would you be able to provide sample qvf or qvw file to look at? It will be difficult to answer without looking at data and data model

[Chanty4u]

Try something like this 

Table:

LOAD

    *,

    YearMonth(Date#(YEarMonthNum, 'YYYY-MM')) AS YearMonth

INLINE [

    YEarMonthNum, Customer_Num_Sales, Income

    2022-05, 25E, 350€

    2022-06, 25E, 176€

    2022-07, 26F, 140€

    2022-08, 35E, 550€

];

FilteredTable:

LOAD

    Customer_Num_Sales,

    YearMonth

WHERE

    Income > 150

GROUP BY

    Customer_Num_Sales,

    YearMonth;

FinalTable:

LOAD

    Customer_Num_Sales

RESIDENT FilteredTable

WHERE

    NOT EXISTS(Customer_Num_Sales, YearMonth - 1, 'FilteredTable');

Result:

LOAD

    Count(DISTINCT Customer_Num_Sales) AS NumberOfCustomers

RESIDENT FinalTable;

DROP

TABLES FilteredTable, FinalTable;