topic Re: Set Analysis - can you spot the error in QlikView

Set Analysis - can you spot the error

alexis — Thu, 06 Sep 2012 15:12:10 GMT

Hi,

I'm creating a group of variables to contain the formulae for some point-in-time requirements.

All variables except one (see below) work and I am sure it's a syntax issue as all fields etc are OK. Can you help?

Here is one that works:

Variable: vSetYTD

Comment: All YTD Sales; MonthID is an incremental ID for all data in my data set. My application comprises "Sales" table and a master calendar - the 2 tables are associated via "Date" field

Usage: In my chart I use it as an expression e.g. Sum($(vSetYTD) Sales)

Definition:

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

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

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

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

THE FOLLOWING DOES NOT WORK:

Variable: vSetPreviousPeriod

Comment: All Sales for the period previous to that selected. For example if they select May2012 and June2012 this should return March2012 and April2012 sales. The user can select 1 or more consecutive months. I am trying to return data whose MonthID is >= to (MinimumID - ((MaximumID - MinimumID) + 1) and less than MinimumID,

Example: Suppose the ID for April2012 is 23, May2012 is 24 and June2012 is 25 then I want to return Sales data for the months whose IDs are 20, 21 and 22 (i.e. Jan2012, Feb2012 and March2012).

Usage: In my chart I use it as an expression e.g. Sum($(vSetPreviousPeriod) Sales)

Definition:

{$<MonthID = {">=$(min(MonthID) - (Max(MonthID) - Min(MonthID) + 1))"},

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

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

ANY HELP would be greatly appreciated.

Kind regards

Alexis

Re: Set Analysis - can you spot the error

whiteline — Thu, 06 Sep 2012 15:37:57 GMT

min(MonthID) - (Max(MonthID) - Min(MonthID)

what is the point ? it (bold) equals sero.

Re: Set Analysis - can you spot the error

alexis — Thu, 06 Sep 2012 16:17:07 GMT

Hi Whiteline,

Thank you for responding to my thread.

In response to your posting I would say, in the first instance to look at the brackets but I thinks it's easier to explain with an example and then we can see if there are ways of simplifying the formula.

My months in my Calendar have a "MonthID", ranging from "1" for the oldest set to whatever for the latest, e.g.

MONTH MonthID

Jan2011 1

Feb2011 2

Jan2012 13

Feb2012 14

Mar2012 15

Apr2012 16

May2012 17

Jun2012 18

Jul2012 19 etc..

I present these months in a listbox and I allow the user to select one of them or a range of conscutive dates.

Let's suppose they have chosen the range Apr2012, May2012 and June2012

The requirement is for a chart showing Sales for the previous period which in this instance would be Jan2012, Feb2012 and March2012.

In the above example:

min(MonthID) would be 16

max(MonthID) would be 18

and I want to see sales for the MonthID range of 13 through to 15.

The formula min(MonthID) - ((max(MonthID) - min(MonthID)) +1)) returns:

= 16 - ((18-16) + 1)

= 16 - 3

= 13 which is the start of the MonthID that I am interested

The maximum MonthID that I am interested is one less than the smallest selected (i.e. 15)

I would welcome all suggestions of how this can be achieved.

Thanks again

Alexis

Re: Set Analysis - can you spot the error

whiteline — Thu, 06 Sep 2012 17:12:40 GMT

sorry haven't noticed the bracket... so that

2*min(MonthID) - max(MonthID) - 1

In the string

"<$(=Date(MakeDate(Min(Year), Min([Month (#)]), 1)),'DD/MM/YYYY'))"

you have 6 open and 7 closing brackets.

Re: Set Analysis - can you spot the error

alexis — Fri, 07 Sep 2012 04:23:59 GMT

Hi Whiteline,

Well spotted regarding the extra bracket. At least now I am getting "something" on the chart (see below) with the corrected formula of:

{$<MonthID = {">=$(min(MonthID) - (Max(MonthID) - Min(MonthID) + 1))"},

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

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

As you can see in the chart below, I selected MonthIDs 25,26 and 27 and should be getting MonthIDs of 22, 23 and 24 as "previous period" - it is almost as if the "MonthID>=" part of the expression above is being ignored and only the "Date<" part is being actioned. Any ideas?

Re: Set Analysis - can you spot the error

alexis — Fri, 07 Sep 2012 04:52:31 GMT

I found the error:

In the formula:

{$<MonthID = {">=$(=min(MonthID) - (Max(MonthID) - Min(MonthID) + 1))"},

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

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

the "=" sign was missing in the first line just after the $ sign -

i.e. it was

{">=$(min(MonthID) instead of

{">=$(=min(MonthID)

Thanks for your help

Alexis