I have several datefields in several tables in my qvw file ^^. I took a look at the master calender etc. (which works fine).. But every solution I try leads to a much bigger problem. For all Dates I create the Fiscal Dates like FY12/13FP05 (Financial Year 12/13 Financial Period 05). Everything works fine in charts etc. if they are isolated. The Problem starts in the Dashboard...
Ok what I want to do: I want to create a central Menu in the Dashboard, where you can choose the Year and Period, like you can see in many dashboards. And in the Charts, QV should use the corresponding Date for the dimension.
Example: If I choose FY13/14FP02(actual period), QV should show me in a chart all Production Orders with Delivery Date at this date. In another Chart it should show me all production orders which are finished at this time. Sounds not like a big problem...
Perhaps I think to complicated, but I need some kind of meta calendar which is above the 2 dimensions. But when I create it for example in a style of a link table, I run into circular references, synthetic keys etc.(synthetic keys are not a real problem...). But when I try to solve the circular reference problem, my dashboard shows really weird values. Also tried a solution with an isolated calendar table and some IF statements in the expressions. This solution works, but it is complicated and it leads to very heavy performance issues(I think because the statement is not just inside one table). By the way, the same expression as Set Expression didn't work!? ...
If you apply this demand on the data model, it will lead to a theoretical inconsistency. A generic date that means delivery date sometimes and finished date otherwise, cannot be used. But there are some work-arounds:
Set analysis (tied to a specific expression) just like Gysbert and others suggest.
thank you for your reply.I really appreciate your help. This would have helped me a lot on friday. I think I found a solution by myself, like you mention as no. 2 of your 3 choices. The solution described by Rob Wunderlich.
I'm aware of the fact with this "inconsistency" you mention. But I think it can be very well controlled in the charts. It's not a typical Order/Sales/Region application. The users just need to know which period they want to look at and in the production environment there is always just the actual day or period. And with individual calendars I think the data can be further reduced if it is neccessary at some point.
Now, I thought a quite long time about this topic and I'm pretty sure in my case it would be more confusing if there are different Date selections.
And it was a good training on data modelling. ^^ I hope it will work like expected in my main QVW.