topic Re: month on month analysis in QlikView

month on month analysis

sudeep_d — Wed, 17 Oct 2012 09:05:22 GMT

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

Wed, 17 Oct 2012 09:11:06 GMT

Hi,

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

Regards,

Janzen

Re: month on month analysis

Gysbert_Wassenaar — Wed, 17 Oct 2012 09:23:18 GMT

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_d — Wed, 17 Oct 2012 09:24:34 GMT

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

sudeep_d — Wed, 17 Oct 2012 09:31:13 GMT

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

johnw — Wed, 17 Oct 2012 20:58:13 GMT

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.