topic Set analysis trouble in QlikView

Set analysis trouble

Wed, 06 Nov 2013 10:48:47 GMT

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!

Re: Set analysis trouble

swuehl — Wed, 06 Nov 2013 10:52:28 GMT

Are your month values numbers or text values, like

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

Re: Set analysis trouble

tresB — Wed, 06 Nov 2013 10:53:24 GMT

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.

Re: Set analysis trouble

Wed, 06 Nov 2013 10:56:58 GMT

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.

Re: Set analysis trouble

tresB — Wed, 06 Nov 2013 11:01:24 GMT

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

Re: Set analysis trouble

Wed, 06 Nov 2013 11:05:40 GMT

Doesn't work like this either..

Re: Set analysis trouble

tresB — Wed, 06 Nov 2013 11:10:03 GMT

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)*

Re: Set analysis trouble

davidrobles — Wed, 06 Nov 2013 11:36:33 GMT

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

Re: Set analysis trouble

swuehl — Wed, 06 Nov 2013 11:41:19 GMT

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?

Re: Set analysis trouble

Thu, 07 Nov 2013 13:56:50 GMT

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