topic Re: Ratio Cumulative customer order by month in App Development

Ratio Cumulative customer order by month

random_user_3869 — Tue, 02 May 2023 14:36:06 GMT

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

Re: Ratio Cumulative customer order by month

Kushal_Chawda — Tue, 02 May 2023 23:19:09 GMT

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

Re: Ratio Cumulative customer order by month

random_user_3869 — Fri, 05 May 2023 09:04:28 GMT

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

;

Re: Ratio Cumulative customer order by month

random_user_3869 — Fri, 05 May 2023 09:05:15 GMT

Anyone has an insight ?

Thank you

Re: Ratio Cumulative customer order by month

Kushal_Chawda — Fri, 05 May 2023 09:16:07 GMT

@random_user_3869 is there specific reason to use loop?

Re: Ratio Cumulative customer order by month

random_user_3869 — Fri, 05 May 2023 09:25:01 GMT

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)

Re: Ratio Cumulative customer order by month

Kushal_Chawda — Fri, 05 May 2023 09:34:25 GMT

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

Re: Ratio Cumulative customer order by month

random_user_3869 — Fri, 05 May 2023 14:00:53 GMT

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

Re: Ratio Cumulative customer order by month

Kushal_Chawda — Fri, 05 May 2023 20:05:26 GMT

@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

Re: Ratio Cumulative customer order by month

Chanty4u — Sat, 06 May 2023 07:01:42 GMT

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;

Re: Ratio Cumulative customer order by month

random_user_3869 — Thu, 11 May 2023 15:29:48 GMT

I'll check this this weekend !

THank you