Not getting Previous Year Sales in Current year sales table after using Intervalmatch()

[venky77777]

Hi Everyone,

Can you help on "to display prev. year sales in same table chart where current year sales is displaying as shown in below image"; Expression for prev Sales=Sum({<Year=, discountFromDate=,discountToDate=, Year={"$(=max(Year)-1)"}>}Sales)

without discountFromDate, discountToDate dimension prev year sales is getting.

Given in script editor:

SalesData:
LOAD * INLINE [
SalesDate, Product, Qty, Sales,Year
2024-01-01, Product A, 10, 100.00,2024
2024-01-05, Product B, 20, 200.00,2024
2024-01-12, Product C, 15, 150.00,2024
2024-01-04, Product D, 5, 50.00,2024
2024-01-05, Product E, 30, 300.00,2024
2024-02-06, Product F, 25, 250.00,2024
2024-01-07, Product G, 40, 400.00,2024
2024-01-08, Product H, 35, 350.00,2024
2024-02-12, Product I, 12, 120.00,2024
2024-01-10, Product J, 8, 80.00,2024
2023-12-15, Product A, 5, 50.00,2023
2023-12-16, Product B, 10, 100.00,2023
2023-12-17, Product C, 8, 80.00,2023
2023-12-18, Product D, 12, 120.00,2023
2023-12-19, Product E, 20, 550.00,2023
2023-12-20, Product F, 15, 150.00,2023
2023-12-21, Product G, 18, 180.00,2023
2023-12-22, Product H, 25, 250.00,2023
];

DiscountData:
LOAD * INLINE [
discountFromDate, discountToDate, Product, Country
2024-01-01, 2024-01-15, Product A, USA
2024-01-05, 2024-01-20, Product B, Canada
2024-01-10, 2024-01-25, Product C, UK
2024-01-15, 2024-02-01, Product D, Australia
2024-01-20, 2024-02-05, Product E, Germany
2024-01-25, 2024-02-10, Product F, France
2024-02-01, 2024-02-15, Product G, Italy
2024-02-05, 2024-02-20, Product H, Spain
2024-02-10, 2024-02-25, Product I, Japan
2024-01-10, 2024-03-01, Product J, Brazil
];

join(SalesData)
IntervalMatch(SalesDate,Product)
load discountFromDate, discountToDate,Product
Resident DiscountData;

join(SalesData)
load * Resident DiscountData;

Drop Table DiscountData;

============Thanks in advance.========

[nhenckel]

The issue arises because your table includes granular dimensions like SalesDate and Qty, which are specific to the current year. These dimensions filter the data to 2024, making it conceptually and structurally incorrect to display previous year (2023) sales in the same table.

If the goal is to show current and previous year sales, the table should focus on aggregated dimensions like Product or Country, rather than transactional details tied to specific years. For example, use Product as the dimension and display measures for current and previous year sales side-by-side. This ensures a meaningful comparison without conflicting filters.

[Kaushik2020]

if you have a date column in your dataset then you can create a DateKey with below code in load editor. 

Floor(DateField) as DateKey. 

Once DateKey is created then link this with the Master calendar. once this is done you can use below code to derive the details.

Sum({$<Year=, Month=, Day=, GA_RP_TYPE={'POSTPAID VOICE'},
Date={">=$(=date(yearstart(Addyears(max(Date),-1))))<=$(=date(addyears(max(Date),-1)))"}>}
Sales)

Highlighted code uses date and it calculates the date range. 

[venky77777]

Thanks for reply, Same applied in KPI, but in table not getting CY & PY sales.

[venky77777]

Thanks for the reply, it works as you mentioned, but i want to exclude the fields discountFromDate, discountToDate in Prev Year Sales, and will be show in table as columns as user requirement(columns as in above image).