topic Set Analysis syntax in QlikView

Set Analysis syntax

hobanwashburne — Wed, 18 Sep 2013 13:07:37 GMT

Bear with me, I'm new to this.

I have the following expression which seems to work for the sum of net sales during fiscal period 201212

=sum({<FiscalPeriod = {"201212"} >}NetSales)

However, the following expression does not work for the sum of net sales between fiscal periods 201212 thru 201302

=sum({<FiscalPeriod = {">=201212 <=201302"} >}NetSales)

What am I missing?

//----------------------------------------------------------------------------------------------------------------

For some reason I can’t reply on
this post… anyway

Sorry to say guys, none of the
suggestions have worked. (BTW Steve, I believe you left out a single quote in
your expression.) I would rather not post the actual file due to size and the
fact that it is our company's financial data. However, I would really like to
know what is going on.

As far as a possible bug goes, I'm
running v10.00.8715.5 IR 64-bit Edition (x64)

I have the dates formatted in the
table load as follows:

Date#(SLGLPR,'YYYYMM') as FiscalPeriod,

and the initial load as

DateFormat='YYYYMMDD';

I changed the initial load to the
following, but it had no effect

DateFormat='YYYYMM';

Re: Set Analysis syntax

Wed, 18 Sep 2013 13:12:36 GMT

Try single quotes =sum({<FiscalPeriod = {'>=201212 <=201302'} >}NetSales).

That should work

Re: Set Analysis syntax

sathishkumar_go — Wed, 18 Sep 2013 13:14:09 GMT

Hi, Could you please attache the application here? Regards Sathish

Re: Set Analysis syntax

Wed, 18 Sep 2013 13:23:39 GMT

Single quotes should do it.

However, I believe I encountered a bug with a similar model of mine, where I could not get the syntax to work out (and I spent days and involved an external consultant who couldn't figure out the issue). So my rather ugly get out was the below formula (only use this as a last resort as I'm sure the performance is far from optimal).

=sum({<FiscalPeriod = {'>=201212'} * FiscalPeriod = {<=201302'} >}NetSales)

The * requests the intersect of the two conditions, i.e. everything between the two dates in this case.

I hope that helps. Let me know how you get on.

Steve

Re: Set Analysis syntax

pgrenier — Wed, 18 Sep 2013 13:28:27 GMT

Good day,

I believe the proper expression would be the following:

=Sum({<FiscalPeriod = {">=201212"} * {"<=201302"} >} NetSales)

Regards,

Philippe

Re: Set Analysis syntax

Wed, 18 Sep 2013 13:35:34 GMT

Good spot Phillippe!!

Re: Set Analysis syntax

v_iyyappan — Wed, 18 Sep 2013 13:37:59 GMT

Hi,

Do u check the FiscalPeriod Dateformat like "201212". If it is not means please change the Dateformat FiscalPeriod as 'YYYYMM'.

And use the expression like

=Sum({<FiscalPeriod={'>=201212 <=201302'}>}NetSales)

Regards,