Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ($)])
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 |
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 ($)]))