Concat if expression

[ramp1985]

Hi Guys,

I'm sure its simple, but can't find the solution.

I have a dataset like this:

Columns:

Ordernumber-Customer-Month-Sales Week-Sales Amount

So, now i want in my table instead of having one line for each month, I want to use Concat to show the months of all orders in the same cell, if sales week was last week.

So it should Concat(combine) the months from all orders on customer level, that was in last week. 

Tried something like: 

=concat(Distinct Aggr(if(

[Customer] = {'=

Sum({<[Sales Week]={$(=Week(today())-1)}>} '} Sales Amount)

>0'}

, Month), Month), '-')

Expected Result:

Hope you can help 🙂

[Or]

If I understood your requirement correctly...

if(Sum({<[Sales Week]={$(=Week(today())-1)}>} '} Sales Amount)>0,Concat(distinct Month,'-'))

Syntax may need tweaking since I can't test it.

[ramp1985]

Hi again,

Thanks for quick help.

Almost there, but. Seems like it also includes months when the costumer has orders older than last week.?  

For example customer 1 has made an order 5 weeks ago. The months on that order is also included, as the customer also has orders last week.

So thats why I think I need to use Aggr but I might be wrong?

[Or · Accepted solution]

Then perhaps I misunderstood your request - my understanding was that  Customer 1 *should* show the months on previous orders, because they made an order in the past week? Wouldn't the months for the past week always be just e.g. March for today (or occasionally, it might cross two months)?

If you want a field iterated only for orders from the past week, there's probably a cleaner way to write it with set analysis, but I think you should be able to use:

Concat(distinct If([Sales Week]=Week(today())-1,[Sales Month]),'-')

Adjust the condition/fields if I wrote them incorrectly, of course.

[ramp1985]

Much better. Last issue, how do I ensure that the earliest month will come first and the latest month will come last? 

Example: MAY-MAR should be MAR-MAY

[Or]

You can apply a sort order in concat(). If your months are duals, you should just be able to use the month. If not, you'll have to use a corresponding MonthNum field or some other mechanism.