topic Set Analysis Expression Not Working in QlikView

Set Analysis Expression Not Working

Anonymous — Mon, 14 May 2012 07:24:53 GMT

What is wrong with my expressions?

why does it show total sales for the entire model?

I can use YearMonth for the Max Month, and it works fine, but I can't for the Prev Month Expression, because it is looking for last year and my model is only for a single year.

What can I do??

MAX MONTH

sum({$<Month={$(=Max(Month))},

Year,

Quarter,

Month>}Sales)

PREVIOUS MONTH

sum({$<Month={$(=Max(Month)-1)},

Year,

Quarter,

Month>}Sales)

Set Analysis Expression Not Working

sivarajs — Mon, 14 May 2012 09:03:54 GMT

i guess that is working can you explain somehow

Set Analysis Expression Not Working

Mon, 14 May 2012 09:34:47 GMT

Hi,

If you select MAY, When you use:

$(=Max(Month)-1), you are getting 4 and Set analysis is waiting for "Apr"

You should use a function that returns month, not number. this happens because the -1 forces a math function with numeric result.

Try this:

Month={$(=Month(AddMonths(%_DateKey,-1)))}

If i were you, I would not use Year and Month separately, but together with MonthYear().

If you do not do this, you will have some troubles while comparing january (could be being compared with NEXT december, not previous).

I Hope this helps you!

Set Analysis Expression Not Working

Anonymous — Mon, 14 May 2012 09:37:49 GMT

the field YearMonth works for the current year,

but if I use the field Month, it shows all sales in the model

Not really sure what else to explain. I guess I don't know why The field month is showing total sales for the model and not total sales for the specific month.

and I can't use yearmonth for previous month, because it's looking for the previous year

Set Analysis Expression Not Working

Anonymous — Tue, 15 May 2012 06:43:11 GMT

diealgon,

Not sure I completely understand what you are suggesting.

are you saying i should use the following expression you suggested directly in the definition of the chart??

Month={$(=Month(AddMonths(%_DateKey,-1)))}

Set Analysis Expression Not Working

Tue, 15 May 2012 12:03:20 GMT

No,

The complete expression in the def of the chart would be like this:

PREVIOUS MONTH

sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter,

Month>}Sales)

Set Analysis Expression Not Working

Anonymous — Tue, 15 May 2012 12:12:05 GMT

the expression

=sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter,

Month>}Sales)

is returning all sales that exist in the model

Set Analysis Expression Not Working

oscar_ortiz — Tue, 15 May 2012 14:56:43 GMT

Chris, Do you need to remove the second Month that you have designated in your Set analysis expression? There is no reason to have that in there twice.

Set Analysis Expression Not Working

Wed, 16 May 2012 07:40:03 GMT

Ok, there was an extra Month= in the Set analysis.

The complete expression in the def of the chart would be like this:

PREVIOUS MONTH

sum({$<Month={$(=Month(AddMonths(%_DateKey,-1)))},

Year,

Quarter>}Sales)

Set Analysis Expression Not Working

Wed, 16 May 2012 07:44:15 GMT

That should work, but this one is better:

sum({$<Month={$(=Month(AddMonths(Max(%_DateKey),-1)))}, Year, Quarter>} Sales)

Set Analysis Expression Not Working

Mon, 28 May 2012 11:50:45 GMT

Hi Chris, ¿did it work?