Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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)
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
;
Anyone has an insight ?
Thank you
@random_user_3869 is there specific reason to use loop?
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)
@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)
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
@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
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;