Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
zsxdd
Sales_tmp:
Load * Inline
[
Date, Customer, Sales, Product
01/01/2017, A, 100, Orange
01/01/2017, B, 120, Grapes
01/01/2017, C, 140, Apple
01/02/2017, A, 40, Orange
01/02/2017, C, 60, Pineapple
01/02/2017, D, 110, Grapes
01/02/2017, L, 80, Pineapple
01/02/2017, P, 90, Grapes
01/02/2017, S, 75, Orange
01/03/2017, B, 45, Grapes
01/03/2017, C, 140, Pineapple
01/03/2017, E, 55, Orange
01/04/2017, A, 60, Grapes
01/04/2017, C, 60, Orange
01/04/2017, F, 75, Grapes
01/04/2017, M, 120, Pineapple
01/04/2017, N, 80, Pineapple
];
Left join(Sales_tmp)
Load
Customer,
min(date(Date)) as FirstSaleDate
resident Sales_tmp
Group By Customer;
Sales:
NoConcatenate
Load
Date,
Customer,
Sales,
Product,
month(Date) as Month,
if(FirstSaleDate=Date,1,0) as [Flag New Customer]
resident Sales_tmp;
drop table Sales_tmp;
Try this
Sales:
Load date(monthname(date#(Datee,'DD/MM/YYYY')),'YYYYMM') as Datee,
Customer,
Sales,
Product
Inline
[
Datee, Customer, Sales, Product
01/01/2017, A, 100, Orange
01/01/2017, B, 120, Grapes
01/01/2017, C, 140, Apple
01/02/2017, A, 40, Orange
01/02/2017, C, 60, Pineapple
01/02/2017, D, 110, Grapes
01/02/2017, L, 80, Pineapple
01/02/2017, P, 90, Grapes
01/02/2017, S, 75, Orange
01/03/2017, B, 45, Grapes
01/03/2017, C, 140, Pineapple
01/03/2017, E, 55, Orange
01/04/2017, A, 60, Grapes
01/04/2017, C, 60, Orange
01/04/2017, F, 75, Grapes
01/04/2017, M, 120, Pineapple
01/04/2017, N, 80, Pineapple
];
NoConcatenate
sales1:
load *,if(Customer=Previous(Customer),0,1) as cscount
Resident Sales Order by Customer,Datee asc;
drop table Sales;
Take dimension as Datee
Expression as Count({<cscount={"1"}>}Customer)
In your script:
Left Join(Sales)
LOAD Customer,
min(Date) as Date,
1 as _New
Resident Sales
Group By Customer;
Create expression in your chart
count(DISTINCT {<_New = {1}>} Customer)
Hi Chanin,
try this
Sales:
Load *,Date(Date,'YYYYMM') as YearMonth,
If(Exists(Customer),0,1) as FlagNew Inline [
Date, Customer, Sales, Product
01/01/2017, A, 100, Orange
01/01/2017, B, 120, Grapes
01/01/2017, C, 140, Apple
01/02/2017, A, 40, Orange
01/02/2017, C, 60, Pineapple
01/02/2017, D, 110, Grapes
01/02/2017, L, 80, Pineapple
01/02/2017, P, 90, Grapes
01/02/2017, S, 75, Orange
01/03/2017, B, 45, Grapes
01/03/2017, C, 140, Pineapple
01/03/2017, E, 55, Orange
01/04/2017, A, 60, Grapes
01/04/2017, C, 60, Orange
01/04/2017, F, 75, Grapes
01/04/2017, M, 120, Pineapple
01/04/2017, N, 80, Pineapple ];
Regards,
Antonio
Thanks a lot .. Works like a charm
From where you got this question?
Why you removed question content?????