Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
jmmayoral3
Creator
Creator

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

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:

jmmayoral3_0-1700052174302.png


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

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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.

View solution in original post

8 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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

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
Creator III
Creator III

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
Contributor III
Contributor III

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
Creator
Creator
Author

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
Creator
Creator
Author

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.