Conditional Calculation of Prior Year Sales

[rishikeshtiwari]

Hi Team,

I have a sample data set where Customer, Product & Time period wise data is available for the last few years.

There is a required scenario where we need to show the reports for

Monthly (month-wise) Current Year v/s Last Year Sales and

Customer & Product-wise Current v/s last Year Sales.

Here we have a condition for Last Year (Prior Year) Sales Calculation i.e we should consider the Sales of Customer & Products for Prior Year which Customer Products having Sales in the Current Year.

It means if there is any Sales value available for Customer&Product in the Current Year, those Customer & Products we should only take for last year's sales calculation on a particular period. This calculation should be dynamic as we have the last 4 years of data.

[GaryGiles]

You need to use the element function P() in your set analysis:

A basic structure to only include prior sales for customer-products in the current year:

sum({$<[Invoice Date]={">=01/01/19<=12/31/19"},Customer=P({$<[Invoice Date]={">=01/01/20<=12/31/20"}>} Customer),Product=P({$<[Invoice Date]={">=01/01/20<=12/31/20"}>} Product)>} [Sales Amount ($)])

I've hard coded the dates above.  It would make it easier if you created an Invoice Year field in the load script.  You could then use a variable to switch between selected current year.

sum({$<[Invoice Year]={$(vSelectedYear)},Customer=P({$<[Invoice Year]={$(=$(vSelectedYear)-1)}>} Customer)

,Product=P({$<[Invoice Year]={$(=$(vSelectedYear)-1)}>} Product)>} [Sales Amount ($)])

[rishikeshtiwari]

Hi Gary,

Thanks for the quick reply. But output does not look accurate. We should have only 31600 $ vales Sales need to show for Monthly Prior Year. Current Year (Selected Year) should always have complete data but data should be reduced for Last Year as per Customer/Product availability in the current year. We should have the below output. Please let me know if you have any conflicts.

Customer	Product	Month	2020 Sales	2019 Sales
C1	P1	Feb	1000	0
C1	P1	Apr	2000	0
C1	P1	Aug	2700	0
C1	P1	Jan	1200	2000
C1	P1	Mar	2000	4000
C1	P1	May	6500	4500
C1	P1	Jul	4000	4500
C1	P1	Sep	5800	4700
C1	P1	Nov	4900	5900
C2	P1	Oct	4000	0
C2	P1	Nov	3000	0
C2	P1	Jul	2700	3000
C3	P1	Jul	2700	3000

[GaryGiles]

Let's try a different approach.

Create an Invoice Month field in the load script with Month([Invoice Date]) as [Invoice Month] and use the following formula for prior year.

sum({$<[Invoice Year]={$(=$(vSelectedYear)-1)}>}
if(Match(Customer&Product&[Invoice Month],$(=Concat({$<[Invoice Year]={$(vSelectedYear)}>}distinct chr(39)&Customer&Product&[Invoice Month]&chr(39),',')))>0,
[Sales Amount ($)]))