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

month on month analysis

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

thanks

• Re: month on month analysis

Hi,

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

Regards,

Janzen

• Re: month on month analysis

Hi ,

no it doesn't depends..

As diplayed sales of may 12 is in previous sales of june 12

thanks

• Re: month on month analysis

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

hi,

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..

thanks

• Re: month on month analysis

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.