2 Replies Latest reply: Jul 5, 2012 9:34 PM by Joao Morais

# What is wrong with this Set Analysis expression?

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

Regards

• ###### Re: What is wrong with this Set Analysis expression?

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?

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.