Set analysis trouble

[#1]

Hi all!

I am new with QlikView and i have a problem with set analysis.

I have to create a variable that brings the average of forecasts for the first 3 months of the year (the first trimester) for each family of products.

The formula I have to create is pretty simple and I think it should be something like this

sum({<MONTH_MONTH_OF_YEAR={1,2,3}>} Forecast)/3

but just doesn’t seem to work

Can anyone help me please?

Thanks a lot!

[swuehl]

Are your month values numbers or text values, like

sum({<MONTH_MONTH_OF_YEAR={Jan, Feb, Mar}>} Forecast)/3

[tresB]

How does the data look like in your field MONTH_MONTH_OF_YEAR? Some other factors might come up. the fastest way to get answer is to share a sample app of yours.

[#4]

The field MONTH_MONTH_OF_YEAR is a number (from 1 to 12)

The variable Forecast is made like this:

$(vACT/FCT)/$(vPYPRICEAVG)*100

Where

vACT/FCT = IF(ISNULL($(vActPrice)), $(vFCTPriceMaterial), $(vActPrice))

vPYPRICEAVG = sum(aggr(($(vPYpriceRicostruito)*
($(vACTFCTQTY)))
/$(vACTFCTQTYMATGRP), MAT_MATERIAL_CODE, MONTH_MONTH_OF_YEAR))

It’s kind of a big cascade here but all others work just fine.

[tresB]

May be your selection in other fields reducing data, try using '1' in set to disregard selections, like:

=sum({1<MONTH_MONTH_OF_YEAR={1,2,3}>} Forecast)/3

[#6]

Doesn't work like this either..

[tresB]

Well, I guess you have to use the same set logic in the other Sum() which comes in variable as well , like:

vPYPRICEAVG=sum({<MONTH_MONTH_OF_YEAR={1,2,3}>}aggr(($(vPYpriceRicostruito)*

[davidrobles]

sum({<MONTH_MONTH_OF_YEAR={'1','2','3'}>} Forecast)/3

[swuehl]

I assume it's not about the set expression for your Month field, but about your forecast variable.

Does this work?

=sum(Forecast)

I assume it doesn't. It's hard to follow your sequence of variables, but please carefully read Henrics latest technical design blog post about variable evaluation in expressions.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

Are you sure the variable expands correctly? And you don't run into trouble using aggregation functions within an aggregation function?

[#10]

I managed to resolve the problem by creating a field in the script that brings me the quarters. I also reviewed the variables and made some changes and now seems to be ok.

Thanks for your recommendation about "The magic of variables", it all seems a bit clearer now