Comparative Analysis challenge!

[alexis]

Hi

I have a requirement that meets the following criteria relating to comparative analysis for a sales related QlikView application:

(a) The comparative analysis needs to be able to compare the selected period with the immediately preceding period and the selected period with the same period in the previous year

(b) The selected period should be any number of continuous months (e.g. either a single month or two consecutive months or three consecutive months etc etc up to 12 consecutive months).

Some examples

(a) If I select May 2012 then the charts should compare May 2012 to Apr 2012 and May 2012 to May 2011

(b) If I select Jul Aug Sep 2012 then the charts should compare Jul Aug Sep 2012 to Apr May Jun 2012 and Jul Aug Sep 2012 to Jul Aug Sep 2011

Can anyone give me some guidelines as to how this can be achieved?

Any assistance/examples would be gratefully received..

Best regards

Alexis

[whiteline]

Use 'set analysis' and also YearToDate() and AddMonths functions.

[jvitantonio]

Alex,

Let's suppose you have a filter with the Year and the Month and you choose April and 2012. In your graph (or table) you need to create an expression like this (the month field is called Month and the year field is called Year):

sum(Sales) <-- This will be the SAles value for the selected period (April 2012)

sum({$<Year={=only(Year)-1}>} Sales) <-- This is the Sales value for April 2011 (Year - 1).

You must study how set analysis work. In the help there's plenty of info. I hope this helps.

[alexis]

Thank you for the feedback.

If the requirement was simply "Choose a month/year" and present me "previous n months" or "this time last year" then I have knowledge to do this with Set Analysis. My problem however is:

a) How to allow the user to select 1 or more (12 max) consecutive months - any tips much appreciated

b) How do I establish what period they have chosen

c) How to present the "previous period" "this time last year" for this variable number of months chosen

Best regards

Alexis

[jvitantonio]

1) the user can select upto 12 months. You don't have to do anything, just put the months in a list box and that's it.

2) It can be or not automatica. Can you post an example applicatino so I can work on it? It sometimes depends the way they are created and the formart data

3) You can do it the with the same formula I posted before. Qlikview is associative, so the months will change automatically depending on what the user chooses. The "last year" thing is handle with set analysis, resting 1 to the year selected.

[alexis]

I do not have an application to send at this point but will post one for you; all we need is some data that has a sale date and amount - I know the "mechanics" of set analysis - the issue is the "variable" number of columns that we are presenting..

i.e

a) if they chose May12 and June12 then we display FOUR columns (March12, April12, May12, June12)

b) If they chose Jan12,Feb12, Mar12 then we display SIX columns (Oct11, Nov11, Dec11, Jan12,Feb12, Mar12) etc

If they would simply accept a 2-column chart ("selected period" vs. "previous period") then I guess this becomes easier, right?

Also how do we ensure they select consecutive months

Regards

Alexis

[alexis]

Here is a demo application that can be used for this issue.

regards

Alexis

[vikasmahajan]

Dear Sir,

I Want to understand How it works can you send me details on this.

Thanks

Vikas

[#9]

You can use somthing on the following lines:-

• Current month versus same month last year

(Sum({$<PeriodID = {"$(=Max(PeriodID))"}, Year = , Quarter = ,

Month = , Period = > } [# Departures Performed])

/

Sum({$<PeriodID = {"$(=Max(PeriodID) - 12)"}, Year = , Quarter = ,

Month = , Period = > } [# Departures Performed]))

- 1

• Current month versus previous month

(Sum({$<PeriodID = {"$(=Max(PeriodID))"}, Year = , Quarter = ,

Month = , Period = > } [# Departures Performed])

/

Sum({$<PeriodID = {"$(=Max(PeriodID) - 1)"}, Year = , Quarter = ,

Month = , Period = > } [# Departures Performed]))

- 1