I have a problem here for a profit comparison between two years. I created a cyclic group using Year/Month/Date as dimension in the bar chart. The Date dimension(in the cyclic group) has an expression which is equal to
The calculated dimension on y-axis of the bar chart represents the profit = margin * volume sold.
The problem happens, for instance, I select April and 2019 both fields, date as the x-axis dimension, then choose one of the products selling. The bar chart shown in the future date (i.e. a date has not been passed) with a high value figure from previous year.
When the product field is deselected, the figures shown in the bar chart is back to correct value. All passed dates their values show on both screens are the same.
If I select on single date, in the bar chat, it looks correct.
I am new to QV technology and I would like someone to tell me how to tackle this problem. By hiding the future date figure using set analysis is not an option.
Unfortunately, I am unable to share the actual app. The master calendar the app used basically derived from a stored procedure in database as shown below. The dimension table used for the calculated expression are also based on the same date format.
I have also attached the data model used the app as below. I suspect the problem is from the OwnSiteDetails dimension not linking directly as other dimension tables.