topic Set analisys compare vs last year problem in QlikView

Set analisys compare vs last year problem

Wed, 18 Jan 2012 21:42:47 GMT

Di Dear Community

I have this problem with this expression :

sum({<Tipo= {'PyG'},Año = {$(=Max(Año)-1)},Mes={"<=$(vMaxMes2)"}>} MVCO_MTOREN)

I'm using the expression in a Pivot Table With thre dimentions (Superior, Group and Subgroup) to try show the value in the last year and accumulate January to month selected.

The expression works fine when in the Year and Month selected there are values (files in the fact table), some cases there are values (files in the fact table), in the last year of year selected and month selected, but there aren't values (files in the fact table), for the selected year and month.

Example

Subgroup Cost value for Aug 2010 --> 1.000

Subgroup Cost value for Aug 2011 --> There aren't values (files in the fact table),

Year listbox value selected 2011,

Month listbox selected Aug,

I Test the expression in a Text Object incluying in set analysis the key for value that aren't values for the selected year and month and works fine

Set analisys compare vs last year problem

Thu, 19 Jan 2012 01:09:49 GMT

Hi,

i don't undrestand very mauch your problem , but ithik if use the month year in set analysis is more simple. you concatenate th emonth and the year and in your expression set analysis you compare the fileds monthyear.

Set analisys compare vs last year problem

Thu, 19 Jan 2012 02:05:52 GMT

Dear blgacem.ejmal

The problem is that the expression does not work well when there no rows in the period you selected in the fields Year and Month, but if there in Year-1 and the same month there are values,

An example with not works fine:

Value Field Selected for Year: 2011
Value Field Selected for Month: August
Value August 2011: (No records)
Value for August 2010: 1000.
Result of expression: The expression NOT show 1000

An example with works fine:

Value Field Selected for Year: 2011
Value Field Selected for Month: June
Value June 2011: 800
Value for June 2010: 1300.
Result of expression: The expression show 1300

Clearly the problem?

Set analisys compare vs last year problem

Thu, 19 Jan 2012 13:44:55 GMT

yes it's clear now

concatenate the month and year filds and try this formula

sum({<MonthYearFields={$(=Max(MonthYearFields)-1)}>} MVCO_MTOREN)

i tik it' more simple than you used month and year

Set analisys compare vs last year problem

Thu, 19 Jan 2012 14:22:17 GMT

I tried the option and not works fine, i tested the expression with explicit values in the Set Analiys {<MonthYearFields={Aug2010)} and for the same example not work well.

Set analisys compare vs last year problem

Thu, 19 Jan 2012 19:12:08 GMT

I solved the problem change some parameters in SA