topic Set Analysis Expression Issue in QlikView

Set Analysis Expression Issue

Fri, 28 Oct 2011 12:13:43 GMT

Greetings my fellowmen and fair ladies!

Am having problems with a set analysis expression.

=count({$<Month={$(=only(Month))}>}TotalOrders)

It should give me the numbers of a previous month (if I select October, besides the actual result, it should return the results of the previous month for comparison).

Can anyone see where have I gone wrong?

Thank you very much in advance guys!

Set Analysis Expression Issue

Fri, 28 Oct 2011 12:25:48 GMT

If your month is a serial number this would work:

=count({$<Month={"$(=max(Month)-1)"}>} TotalOrders)

If not, you can create the serial number for month as (num(Month)+12*(Year-2006) assuming 2006 is your least year)and name it as MonthID.

In your function only(month) is returned only when one month is selected, but max will give you most recent for the selection which I think is better (Ofcourse you can reason it as business needs). Secondly you need to have only(Month)-1 for previous month.

Regards,

Kiran.

Set Analysis Expression Issue

Fri, 28 Oct 2011 13:24:26 GMT

Kiran, thank you very much for your reply!

But it havent worked... follows the screenshot

I think there's something wrong with the month... may be not considering something regarding it...

Can you see were have I gone wrong?

Thank you SO much!

Set Analysis Expression Issue

Fri, 28 Oct 2011 13:34:23 GMT

Just a format issue. Try the below set analysis:

{<Month={"$(=month(Only(Month)-1))"}>}

Kiran.

Set Analysis Expression Issue

Fri, 28 Oct 2011 13:41:56 GMT

Kiran, have tried but didn't work....

The Only(Month)-1 part is returning a number (in the example, '9')... could it be the problem?

Set Analysis Expression Issue

Fri, 28 Oct 2011 13:45:37 GMT

Check out what month(Only(Month)-1) is returning. It should return Sep for the expression to work.

If that doesnt work try month(Max(Month)-1))

Regards,

Kiran.

Set Analysis Expression Issue

swuehl — Fri, 28 Oct 2011 13:58:15 GMT

Please note that month(Date) takes a Date as parameter.

Month(9) will probably return January (of year 1900).

Thiago, have you tried just removing the quotation marks (indicating a search string)?