Hi,
Please can someone help with my query?
I want to allow users to:
- select to view data by calendar or fiscal dates
- select an analysis date field (this could be 'order date', 'date required' etc. which are all in different tables)
- select a period of analysis (day, month, quarter, ...)
and then view the sales for those selections in a pivot or straight table
I started doing this with a master calendar with fields such as calendar month, fiscal month, calendar year, fiscal year etc. However this was linked to only one date field and so did not allow point 2 above.
I then tried to use island tables with one for calendar / fiscal view and one for analysis date view. These had one column and the entries consisted of the values that the user will select: i.e. the analysis date island table was one field called 'Analysis Date' with entries 'order date', 'date required' etc. I then used a variable with an if statement to pick the right field as a dimension in the chart (i.e. if Analysis Date = 'order date' then use the order date field). I then created a load more variables to pull the month, quarter, year etc. for the date selected. This worked perfectly for points 1 and 2 above although the table was slow to load. However I can't achieve point 3 above as the period analysis 'fields' are all variables.
Can someone please help me? There must be a smart way to do this.
Thanks.
