Data from the same day of the previous year with missing dates

[jmmayoral3]

I have a table in memory with 3 fields: [Accounting date], sales and [Previous Year Date]
The [Previous Year Date] field is the date equivalent to the same day of [Accounting date] in the previous year.
I need to make a simple table chart with this information:

The "Previous Year Sales" column is an expression that cannot be precomputed and shows sales information on the same day in the previous year, so if nothing was sold on that day in the previous year, it displays a 0.
It cannot be precalculated because it must depend on the selections made by the user and this can cause missing dates (for example, we see that we are missing November 12 and 11, 2022.

how can I create the column  "Previous Year Sales" with an expression with set analysis that relates [Accounting date] to [Previous Year Date] ?

thanks in advance

[Stoyan_Terziev]

Hello,

Here is  a simple way where you don't have to patch your data with the missing dates. Keep in mind that dates missing in both current and PY will not be in your dataset.

SalesData_temp:
LOAD
	[Accounting Date]
,	Sales
From YourSalesQVD.qvd (qvd)
WHERE [Accounting Date]=Year(Today());	//Get current year sales

Concatenate (SalesData_temp)
LOAD
	Date(AddYears([Accounting Date],1)) as [Accounting Date]
,	Sales								as [PY Sales]
From YourSalesQVD.qvd (qvd)
WHERE Year(AddYears([Accounting Date],1))=Year(Today());	//Get previous year sales

SalesData:	//Aggregate To bring the values on the same line
NoConcatenate
LOAD
	[Accounting Date]
,	Sum(Sales) 			as Sales
,	Sum([PY Sales]) 	as [PY Sales]
Resident SalesData_temp
GROUP BY
	[Accounting Date];
	
DROP TABLE SalesData_temp;	//Remove the initial table

Then the set expression can look like this:

Sum({<[Accounting Date]= {"<=vUserSelectedDate"}>}[PY Sales])

where the vUserSelectedDate holds the user's current selection date.

[Stoyan_Terziev]

Sorry, I forgot to mention that if the Date you want to put values on from PY does NOT exist in your dataset you cant do that. You need to make sure your calendar has the date.

[jmmayoral3]

Thank you very much Stoyan for your quick answer, but the solution doesn't work for me because, as I said in the statement, I can't calculate it in the script. I have to calculate it on the fly with an expression in the simple table graph.
The data in the question is just a simplified example to understand things more easily, but really my table, apart from those 3 fields, also has 32 others, among them the customer_id, product_id, country.... and the order_id , which is unique and never repeated. Therefore I cannot apply the solution you propose.

[marcus_sommer · Accepted solution]

For me it's not quite clear which dates should be matched against each other - the calendar-dates, working-dates or any other ones (which means calendar/working-dates with more or less exceptions/adjustments) - and very important how NULL respectively not existing sales should be handled? NULL/ZERO results are not mandatory an error and may show (respectively being the opposite) rather the truth as adjusting them in any way.

Beside this I wouldn't touch the facts - unless my wanted views really require to populate non existing sales with NULL/ZERO records - else creating the appropriate information within the calendar. The most simple scenario would be just to create there a second date-column without a year-information, maybe per:

dual(day(Date) & '. ' & month(Date), daynumberofyear(Date)) as DateWithoutYear

and using it as vertical dimension and Year becomes the horizontal dimension and/or is specified within a set analysis which means just sum(Sales) as expression would be enough to get the comparing view against the previous sales.

[Aasir]

Two ways to do this.
First method - If you have the 3 mentioned fields in the do the below in data load.

LOAD
[Accounting date],
Sales,
[Previous Year Date],
ApplyMap('SalesMap', [Previous Year Date]) as [Previous Year Sales]
FROM [your_data_source];

SalesMap:
Mapping
Load
[Accounting date] & '|' & [Previous Year Date] as Key,
Sales as [Previous Year Sales]
Resident YourTable;

2nd method in visualization:
aggr(Sum({< [Accounting date] = {'$(=Date(AddYears([Accounting date], -1)))'} >} Sales),[Accounting date])

[Kat92]

Hi,

What you could try if you want the calculation in set analysis is the above function.

Below you may find the example, I have also added a step for the date not to be affected by filters, so that you can present in a chart the whole year.(you can remove if you need)

{<Date= >} (above(sum( {<Date= >} [Sales]),12,3))

[jmmayoral3]

Thanks, Stoyan.

It could be a good solution, but really in my dataset I do not only have those 3 fields, but many more, which would force me to relate each combination of values ​​of each field with all the others for each date.

[jmmayoral3]

It's not the solution I was looking for, but it's a great idea.
I have implemented it and it works.
I have created the following 3 columns in the calendar: Accounting date (which links to the fact table), year and Day (the latter only in MMMM-DD format). Then I created a simple table with this "Day" as dimmension and 2 expressions with set analysis, one for the current year's sales and another for the previous year's sales. This way I fulfill the condition that it must respond to the filter selections.