Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My database has 2 dimensions and the sample data is as follows:
ECI Date
111 11/01/2016
111 12/01/2016
113 11/01/2016
113 11/01/2016
115 12/01/2016
115 10/01/2016
So I want to identify only those values in ECI which has appeared in the previous month too?
Therefore the answer for this would be 111.
Any suggestions on the formula?
Cheers,
Rohan Shetty
Create a MonthName field (would specially be required when you would have to compare inter-year values) in the script like:
Load *,
Month(Date) as Month,
MonthName(Date) as MonthName
Inline [
ECI, Date
111, 11/01/2016
111, 12/01/2016
113, 11/01/2016
113, 11/01/2016
115, 12/01/2016
115, 10/01/2016]
Expression used like:
=Concat( DISTINCT {
<ECI=p({<MonthName={'$(=MonthName(Max(Date)))'}>}ECI)>
*
<ECI=p({<MonthName={'$(=MonthName(Max(Date),-1))'}>}ECI)>
} ECI)
Create a MonthName field (would specially be required when you would have to compare inter-year values) in the script like:
Load *,
Month(Date) as Month,
MonthName(Date) as MonthName
Inline [
ECI, Date
111, 11/01/2016
111, 12/01/2016
113, 11/01/2016
113, 11/01/2016
115, 12/01/2016
115, 10/01/2016]
Expression used like:
=Concat( DISTINCT {
<ECI=p({<MonthName={'$(=MonthName(Max(Date)))'}>}ECI)>
*
<ECI=p({<MonthName={'$(=MonthName(Max(Date),-1))'}>}ECI)>
} ECI)
Hi tresesco,
The expression u provided works perfectly but I need a count of the total ECI rather than the final value.
I am sorry for the mistake I made while raising this question.
I replaced concat with count. It works.
thanks
Just another clarification
I have another field in the same table but it is unable to filter the data according to the field.
I have attached the sample
I understand from the data that 2964 and 4499 are present in all the Customer Data Quality dimension.
But only in CIP City is it occurring every month
I hope I am able to explain it clearly.
Cheers,
Rohan