Hi
"I want to have a common filter of Fiscal year and Fiscal Quarter that I can use on Report to have a CanonicalDate as a common Dimension."
I also would like to do this. And you can if and only if you can link the tables and use a canonical date. I try to do this but at times it is not possible. As in your example above. You can not do it for the activity date (it seems)
So in this situation I use a canonical date + a calendar Island approach. You can use a calendar Island for everything (rather than a canonical date) but it has the limitation that you can NOT use these calendar fields for a visual dimension (they can only be used for making data filter selections) as you have tried to above. And it's slower and uses more memory to make filter selections in large files.
So what to do in this situation. One option is to set up a calendar / the required date fields for the activity month in script and use this as a dimension --> month (Activity Date) as MonthActivity. This has the drawback that users can use this month dimension as a selection in error. So I tend to call it something like --> month (Activity Date) as MthACTIVITYONLY and rename it when I use the dimension in a visual. So a user will see a strange selection if they use this and hopefully will remove it when they move from one sheet to another one not using activity fields.
Another is just to use this type of formula in the expression field in the chart--> month ([Activity Date]) . But this still has the issue that users can click on this dimension in the visual and then it becomes a selection. So I tend to go for the script approach.
There might be other ways of doing this but I have not come across a good one yet.
Re the set analysis. Get it working first and then try to follow the logic. But you must have this field in the set analysis for any canonical fields selection made. So if a user filters by a FiscalQuarter selection then FiscalQuarter must be in the set analysys
count ({$< [Activity Date] = P(CanonicalDate) , Day = , Month = , Year = , FiscalYear = , FiscalQuarter = , FiscalMonth = >} ActivityID)
But I'm not an expert on this. Please let me know if it works and mark replies as answered / helpful if one solves you first question.
