Carve out a group of customers in Dimension and get historic data

[aetingu12]

Dear all,

Apologies in advance for not being very descriptive in my title. 

I have a year worth of data on Customers, Locations and Sale amounts.  I would like to carve out Customers in February 2018 from France in my dimension and use this specific list of customers to get all the available data from Februart onwards.  Basically I am trying to tag a specific list of customers and track their progress going forward.

I believe I need to do this with an aggr() function within the dimensions, but not sure how.

Your help is much appreciated.

Regards,

[sunny_talwar]

May be use set analysis instead of a calculated dimension. Let's say you have an expression like this

Sum(Measure), try to use this

Sum({<Customer = p({<Country = {'France'}, MonthYear = {"$(=Date(MakeDate(2018, 02, 01), 'MMM-YYYY'))"}>})>}Measure)

Where MonthYear is calculated in the script like this

Date(MonthStart(DateField), 'MMM-YYYY') as MonthYear

[aetingu12]

Thank you for quick come back.

I need this to be in dimension, because by filtering on these customers, I will be running calculations on them.

Basically, I want to get a list of French Customer in February and calculate rolling averages going forward just for these select customers from February all the way up to the end of the year.

Hope that makes a bit more sense.

[sunny_talwar]

Why can't you do this using an expression? I may not understand the whole problem, but from what I have heard thus far, it seems that expression might still work and will generally work better than a calculated dimension.

[sunny_talwar]

But if you really want a calculated dimension... may be this

Aggr(If(Country = 'France' and SubStringCount(Concat({1} DISTINCT '|' & MonthYear & '|', ','), 'Feb-2018'), Customer), Customer)

[aetingu12]

After thinking about it, I think you are correct.  My logic was making it more complicated that it actually was.  I will go with your first recommendation.  Thank you.

One last thing, I do not have access to the scripting as it is locked for us.  If I were to create the DateYear in a new list expression like you have formulated, can I refer to that within the set analysis? ie. I create a new List from expression:

Date(MonthStart(DateField), 'MMM-YYYY') and call it MonthYear and how do I refer to that from within Sum({<Customer = p({<Country = {'France'}, MonthYear = {"$(=Date(MakeDate(2018, 02, 01), 'MMM-YYYY'))"}>})>}Measure)

Thanks again for your patience, I will need to read upon set analysis to understand it better.

[sunny_talwar]

When you say list expression, do you mean list box expression? That won't work... what sort of date field do you have? What is the format of the date field?

[aetingu12]

Yes, list box expression. I have a date field formatted as 28/02/2016  (sorry unfortunately my corporate environment blocks everything so it is difficult to get more detail on the creation of these fields.)

[sunny_talwar · Accepted solution]

Try this

Sum({<Customer = p({<Country = {'France'}, MonthYear = {"$(='>=' & Date(MakeDate(2018, 02, 01), 'DD/MM/YYYY') & '<=' & Date(Floor(MonthEnd(MakeDate(2018, 02, 01))), 'DD/MM/YYYY'))"}>})>}Measure)

[aetingu12]

Thank you very much for all your help and patience!