There are several options to go about resolving your issue, I will propose two, the first one resolve your problem on the script, the second implements a solution on the front-end user interface.
I created a mocked QVD files with two years of sales transactions (date-sales) and a calendar as part of the solution attached (50.Split Transactions.qvf).
Script Solution:
We concatenated year 2019 transactions, renaming their date (Today) and sales fields to Sales_Date_2019 and Sales_Amount_2019 with year 2020 transactions, renaming them as Sales_Date_2020 and Sales_Amount_2020.
The concatenation logic introduced the Date_Index field, its expression for both sets of transactions, is: 2019 + Month("Today") + Day("Today"), this field has a common value for transactions recorded on each year.
After concatenating the sales transactions, splitting them in columns by year, a summary is calculated based on the Date_Index field, returning the Min() or Max() on the date columns (It does not matter if you use Min or Max), and the Sum of the Sales figures per year; this summary flatten the data, so transactions on the same date in both years will be on the same row.
The Calendar table also feature the Date_Index field calculated with the same expression.
User Interface (Front-End) Solution:
The script does not split-concatenate-summarize the data. All this transformation is done with a Table.
The first column on this table was named "Index", it has the expression: 2019 + Month("Tran_Date") + Day("Tran_Date")
Note: The table with all the sales transactions features the columns Tran_Date and Tran_Sales to avoid synthetic keys.
The expression for the column: Sales_Date_2019 is: Max(If(Year(Tran_Date)= 2019, Tran_Date))
The expression for the column: Sales_Amount_2019 is: Sum(If(Year(Tran_Date) = 2019, Tran_Sales, 0))
The expression for the columns Sales_Date_2020 and Sales_Amount_2020 applied filters for the appropriated year.
Considerations:
My sales transactions are continuous in each year without gaps, it should not be a problem if the data contains gaps (a gap is missing days).
You may need to adjust the expression used to calculated the Index column, I based its starting value on the year 2019.
The logic is valid to show sales transactions side by side.
The years 2019 and 2020 are hard-coded in the columns names, for a generic solution you should figure out a way to make them dynamic.
Attachments:
The qvf application with its two QVDs.
Hope this helps,
Arnaldo Sandoval A journey of a thousand miles begins with a single step.
