I want to be able to compare data in a chart with previous period. Depending on what date the selection is, it should compare with the dates up to last period.
For example, let's say I have sum(sales).
If I select year 2014, I want to compare sum(sales) for all time up to and including year 2014, with sum(sales) for all time up to and including year 2013.
If I also select month Feb, I would like to compare sum(sales) for all time up to and including 2014-02, with sum(sales) for all time up to and including 2014-02.
Same with week...
This should be done in the same chart, all depending on the users choice of date..
One thought is to have your chart use an if() statement to choose which set of code to use for the time period selected. Therefore, if year is selected, run the year code, if month, then the month code. In your set analysis to do the calculation you would do something like:
sum({$<Year = {'<=$(year(today))'}>}sales)
and the comparison year as :
sum({$<Year = {"<=$(year(today)-1)"}>}sales)
You would then use your if statement to run that code if the year is selected; if it was month, you would run the set analysis for month, and week for week. Would this work?
You mean to first check something like if Year is not null, or if getcurrentselecions() include year, then do the scripts for year, and same for month etc? I guess that could work.
I would need to exchange year(today) with year of current selection, what code can I use here?
If you need the user to select year 2014, and month = 'February', you would also have to make sure that you use the Month-year style of date, not just month number. Otherwise it would show you 2014 January and February, 2013 January and February, 2012.. etc, etc. You would just need to say that the month-year combo is less than the current selected month-year combo.
Since it needs to be based on the user's selection it may need to be something like:
