Sum if Zero for 3 years

[tandrisani]

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!

[Stoyan_Terziev]

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.

[sunny_talwar]

May be this

Dimension

Customer

Expression

Sum({<Customer = P({<Year = {"$(=Max(Year))"}>})-P({<Year = {"<$(=Max(Year))"}>})>} Sales)