Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a request to provide a sales report and I'm struggling with the Set Analysis. The request is to show items sold to a specific customer THIS YEAR but NOT in the last three years. In the example below, I would only want to know that Customer A purchased Item 12345 this year for a value of $50.
Customer | Part | Sales 2016 | Sales 2017 | Sales 2018 | Sales 2019 |
Customer A | 12345 | 0 | 0 | 0 | 50 |
Customer B | 12345 | 0 | 10 | 0 | 50 |
Customer C | 12345 | 10 | 20 | 30 | 0 |
Ideally, I'd like to be able to provide a list of Customers with all the items they purchased this year along with the sales value of those items for this year.
Thank you!
Hello Tandrisani,
I'd advise you to do a Cross Table() on the back end so your table looks like this:
Customer | Part | Year | Sales |
Customer A | 12345 | 2016 | 0 |
Customer A | 12345 | 2017 | 0 |
Customer A | 12345 | 2018 | 0 |
Customer A | 12345 | 2019 | 50 |
And then on the front-end create a chart with a measure which will be something like:
=If(Sum({<Year={2019}>}Sales)<>0 and Sum({<Year={'*'}-{2019}>}Sales)=0, Sum({<Year={2019}>}Sales), 0)
Simple approach now will be:
You can create a chart with the dimensions
Customer | Part |
And the measure:
If((Sum([Sales 2016])+Sum([Sales 2017]) + Sum([Sales 2018]))=0, Sum([Sales 2019]), 0)
Keep in mind the upper ideas are NOT dynamic, I'm guessing you don't need them to be.
I hope this helps!
Kind regards,
S.T.
May be this
Dimension
Customer
Expression
Sum({<Customer = P({<Year = {"$(=Max(Year))"}>})-P({<Year = {"<$(=Max(Year))"}>})>} Sales)