topic Re: What is wrong with this Set Analysis expression? in QlikView

What is wrong with this Set Analysis expression?

Wed, 04 Jul 2012 21:19:10 GMT

Hi all,

I appreciate if you could check the expression below, and the set analysis part of it, and tell me what's wrong with it.

Sum( {< [Transaction Type] = {'Stocks'}, Date= {$(=concat(distinct QuarterStart(Date), ','))} >} lUnits)

I need to evaluate the stocks level by period - either monthly, quarterly and yearly, and for that thare is the need to only consider the last month included in the time dimension chosed.

That is to say that if the period dimension chosed is month than the monthly stocks must be represented.

If the period dimension chosed is quarters than the last monthly by quarter stocks must be represented and so on.

Example:

Dimension: Quarters

Listed QTR members Month for data

Q1-12 Mar-12

Q2-12 Jun-12

Q3-12 Sep-12

Q4-12 Dec-12

Appreciate all your help

Regards

Re: What is wrong with this Set Analysis expression?

sbaldwin — Thu, 05 Jul 2012 10:27:13 GMT

hi it is difficult to say without the qvw to look at, but i would say it is your date that is causing you problems as the concat function will not place quotes round the date strings, try making a copy of the date field but formatting as a number and substitute this in? alternatively try putting the quotes into you $ expansion, something like :

$(=chr(39) & replace( Concat(distinct quarterstart(Date), ',' ) ,',', chr(39) & ',' & chr(39) ) & chr(39))

Re: What is wrong with this Set Analysis expression?

Fri, 06 Jul 2012 01:34:26 GMT

Hi Sbaldwin,

I had to abandon this strategy once it leads to loose some data in the tail areas of the time dimensions - mainly when the quarters are not complete which happens when the available periods are the month 2 or 3 of a quarter.

Anyway, please find attached the qvw with a demo issue.

All your help is appreciated

Regards