Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Previous sales is based on the same month but last year. Am I right?
Regards,
Janzen
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)
Hi ,
no it doesn't depends..
As diplayed sales of may 12 is in previous sales of june 12
thanks
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
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.