Forum Discussion
AdministratorVisualize annualized data along with monthly data.
How is it going!
I want to visualize a table in the report that mixes a list of items, each one should show two groups of data:
- Values per channel obtained in the selected month.
- Values per channel generated in the previous year.
The relationship of the category, brand and values tables works wonders for me, but the issue is that the table with the historical one does not handle monthly values, but annualized ones.
With this we would be comparing what each item generates month by month, vs the average generated in the previous year. This is necessary since the client wants to see if, for example, in the month of January 2022 the Coloret item generated ER 6.9, what was the average of that same item during 2021 and compare it.
The structure of the table with the ER values of each item is monthly, while that of the historical is annualized.
Is there a way that when you filter by month, it does not affect the visualization of the historical data, which is structured in the same table?
Data visualization marking the year 2021 and the month of January:
Data visualization marking 2021 and the month of February. (No longer displays historical data)
In advance thank you for the help.
1 Reply
- lbendlin
Super User
1. You must have a Calendar table (contiguous, covering)
2. You can use any of the Time intelligence functions like SAMEPERIODLASTYEAR but you have to be aware that these always filter on whole periods
3. for a better, "to date" comparison you must add a flag to your Calendar table that indicates what "to date" means in your context. It will still not be accurate as the weekday pattern differs between periods, but it will be the result with the highest level of truthiness.
