Show our Customers' Sales within each Year after Our Sales Date to them

[desertmatt]

Dear Community,

I want to analyze how long it takes till our customers sell our products invoiced to them in various years.

The final Table should look something like this 

It seemed so simple but it's not happening

I created in the load script the Year fields from the Invoice dates

SalesDateYear as Year(Date(InvoiceDate)) (the Date when WE sold to our customers)

SoldDateYear as Year(Date(CustomerSoldDate) (the Dates when our customer sold to their customers)

No Selection.

As Dimension I'm using the SalesDateYear (2014, 2015, 2016, etc.)

In the expression for the different Years within and  after our Invoice Year, I tried:

=Sum({< SoldDateYear = {"=AddYears (SalesDateYear,1)"}>} SalesPrice)/Sum(SalesPrice)

but this is not working all other trials also failed (like aggr over the SalesDateYear etc.)

I'm sure for our experts here this is very easy but it escapes me...

Your help would be VERY much appreciated

Thank you very much

Matt

[sunny_talwar · Accepted solution]

This

=Sum({<InvType-= {"Memo"}>} If(SoldateYear=SalesDateYear+2, SoldPrice))

As a general rule, Set analysis can only be used within an aggregation function... so use it right after Sum(... rather than within your If statement 

[desertmatt]

THANK YOU SUNNY! - AGAIN!

Thanks also for the explanation - so much too learn...

Also still don't really understand why we need the If() solution here - and the Set Expression with addyears didn't work...

I really appreciate you're taking care.

Kind regards

Matt

[sunny_talwar]

Set analysis works once per chart and not per dimension.... you were comparing your SalesDateYear to SoldDateYear based on the value of SalesDateYear... so if SalesDateYear = 2014... you want to compare it to 2014, 2015, 2016, 2017, 2018... and when it is 2015, you want to compare it to 2015, 2016, 2017, 2018, 2019... and so on...but you can do this within set analysis... so in order to do a dimension wise comparison... if statement was needed...

[desertmatt]

Thank You again Sunny!

"Set analysis works once per chart and not per dimension..." that is the essential insight - and I don't recall to have read this so clearly anywhere in the documentation...

Thank you Sunny - big help - really!

Matt