5 Replies Latest reply: Oct 17, 2012 5:04 PM by John Witherspoon RSS

    month on month analysis

    sudeep dhoju

      Hi ,


           monthyear       currentsales          previous sales

                   apr12        300                              200

                   may12       500                             300

                   jun12         1000                           500


      if i want a table which looks exactly like the above where monthyear is my dimension and currentsales and previous sales is my expressions,

      what should b the expression for the previous sales



        • Re: month on month analysis



          Previous sales is based on the same month but last year. Am I right?





          • Re: month on month analysis
            Gysbert Wassenaar

            I assume you have a Month and a Year dimension too. If not you should create them. Then the current sales would be:

                      sum( { $<Year={$(=only(Year))},Month={$(=only(Month))}> } sales)


            and previous sales would be:

                      sum( { $<Year={$(=only(Year))},Month={$(=only(Month)-1)}> } sales)

              • Re: month on month analysis
                sudeep dhoju


                yes i have year and month fileds too .. but the expression given by u is also not workin..i cannot use above or previous functions cuz it comes wrong when i select a single month..


                  • Re: month on month analysis
                    John Witherspoon

                    Set analysis is performed once for an entire table, so can't* be sensitive to the rows of the table, so can't* actually build the chart you're asking for, at least not directly.


                    Attached is an example using a number of different approaches.  The pivot table with AsOf is my suggestion, and if you need to see growth, for example, then I suggest following the set analysis chart in the example.  A drawback is user confusion if both the AsOf and regular data are available for selection.


                    Other common approaches shown are using a date island or using the above() function.  The date island will have performance problems on large data sets, and can have the same sort of user confusion as the AsOf table.  The above() function will fail if you select a specific year.


                    So no perfect solutions, just workable solutions with pros and cons.


                    *Technically there is a way around this limitation, but it is very complicated, and I have yet to find a practical application.