Set Analysis and Point in time

alexis — Thu, 06 Sep 2012 10:04:13 GMT

In my application I have Sales data and a Master Calendar (containing all the usual Day, Month, Quarter etc) that is linked to the Sales table by a field called "Date".

I present to the user a listbox of MonthIDs and based on their selection I present some "point in time" statistics like "Month to date" "year to date" etc. This is achieved through the use of variable definitions such as in the following examples:

Example 1:

I have a variable called vSetMTD whose definition is:

{$<MonthID = {$(=Max(MonthID))},

Date = {"<=$(=Max(Date))"},

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

In my chart the expression would be simply:

Sum($(vSetMTD) Sales)

Example 2:

I have a variable called vSetPreviousYearMTD whose definition is:

{$<MonthID = {$(=Max(MonthID) - 12)},

Date = {"<=$(=Date(MakeDate(Max(Year) - 1, Max([Month (#)]), Day(Max(Date))),'DD/MM/YYYY'))"},

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

In my chart the expression would be simply:

Sum($(vSetPreviousYearMTD) Sales)

etc....

QUESTION:

I now need to extend the functionality to allow them to choose a range of months from the MonthID listbox and for my chart to show:

a) Sales for Selected Period:

This is easy: it is simply Sum(Sales)

b) Sales for Previous Period:

This is based on the idea that if they chose 3 months (say MonthIDs 12,13,14) then the Previous Period would be MonthIDs 9, 10 and 11

I am trying to create a variable definition (as those above) which defines the following logic:

MonthID >= Min(MonthID) - (Max(MonthID) - Min(MonthID) + 1)

Date < MakeDate(Min(Year), Min(MonthNo), 1)

c) Sales for Selected Period Previous Year

Same issue as above

I have attached an example application thta can be "played with"

Thanx in advance

Alexis

topic Set Analysis and Point in time in QlikView

Set Analysis and Point in time