topic Re: Set analysis-Include excluded values in QlikView

Set analysis-Include excluded values

Mon, 27 Jan 2014 16:20:48 GMT

Hi,

I want to write a sum() expresion that include excluded values. How can i do it?

when i write sum({1<>}), it returns sum of all of possible values. I want to include grayed values.

Re: Set analysis-Include excluded values

Mon, 27 Jan 2014 16:30:19 GMT

Hello Sana,

I am assuming that you want to ignore the grayed values of one field. If so, this is the expression.

Sum(${<FIELD_TO_IGNORE = {} > }SALES)

Re: Set analysis-Include excluded values

Nicole-Smith — Mon, 27 Jan 2014 16:30:32 GMT

You want to look at the E() function in set analysis.

Example:

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )

returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

Re: Set analysis-Include excluded values

Mon, 27 Jan 2014 16:35:54 GMT

Hi shankar,

No it's not working

Re: Set analysis-Include excluded values

Mon, 27 Jan 2014 16:37:35 GMT

No, i don't want to exclude specific customers. All i want is to include in my sum customers that never bought the product Shoe, even when i select this product from the filter

Re: Set analysis-Include excluded values

Carlos_Reyes — Mon, 27 Jan 2014 16:40:28 GMT

As Nicole stated, the excluded values can be interpreted depending on the rules of selections, so you can add another selection in Set Analysis to indicate which values will be considered as "excluded" or you can just use the current selections. For instance:

sum( {$<Customer=E(Customer)>} Amount)

The above expression will show the sum of amount for all the Customers that are excluded given the current selections.

Also you could see select all excluded field values with the following set:

sum( {1-$} Amount)

You should review the Set Analysis description in the Help.

Re: Set analysis-Include excluded values

MK_QSL — Mon, 27 Jan 2014 16:41:04 GMT

sum( {$<Customer = E({1<Product={‘Shoe’}>}Customer)>} Sales )

Re: Set analysis-Include excluded values

Mon, 27 Jan 2014 17:01:50 GMT

Apparently no one understood my need.
I explain, assuming that when I select 2013, I got only January sales. (Only January has sales)

In my graph I want to display:

-the January sales

-forcasted sales (FCT) of all 2013.

How can I write this FCT expression?

Re: Set analysis-Include excluded values

Carlos_Reyes — Mon, 27 Jan 2014 17:10:14 GMT

That's a completely different question... and it also depends on your rules to forecast. The simplest method I've seen is to use the previous period and use a factor (defined by your bussines rules) to create the forecast for the current year. What's more often is that people get this value from a database table...

P.S. I don't see how using the excluded values will create a valid forecast...

Re: Set analysis-Include excluded values

Tue, 28 Jan 2014 07:45:00 GMT

I have alreday the fcsted values, for each month.. This is not the problem

The problem is , as i said: if there are sales only for one month (January), it will calculate only the fct of january. But i want to display the fct of all 2013, see that i only select the year 2013 and not the month.

So here, Including excluded values=include all 2013 months in the sum of fcted values (even grayed months Feb-->Dec)

Re: Set analysis-Include excluded values

jonathandienst — Tue, 28 Jan 2014 08:00:31 GMT

I think the answer to your question depends on the structure of your data model. It will be easier to suggest a fix and less testing for you if you could upload your model (or a sample if it large).

To upload, click the Use advanced editor link at the top right of the Reply form.

HTH

Jonathan

Re: Set analysis-Include excluded values

Tue, 28 Jan 2014 08:48:36 GMT

as Jonathan said it depends on model and field you are using for selection

So if you have a Calendar table you may use it to select 2013

you will have then Sales for 2013 (january only) and all forecasted monthes

best regards

Chris

Re: Set analysis-Include excluded values

Tue, 28 Jan 2014 09:22:35 GMT

Thanks christian, it's working

Re: Set analysis-Include excluded values

sunilkumarqv — Tue, 28 Jan 2014 10:25:11 GMT

Hi,

see the following set analysis for included Year={$(=Max(Year))} and excluded Field -= {'value'}

=((Sum({<Year={$(=Max(Year))},Field -= {'value'}>}Amount))

Regards

Sunil

Re: Set analysis-Include excluded values

bvssudhakar — Wed, 04 Apr 2018 07:33:43 GMT

Hi Nicole Smith,

I have one requirement, here i attached one qvw file in that file

If i select for filter 1: Baby Clothes in Category Name, Aino Shoes from Product name, 2012 from Year and

for filter2:Baby Clothes in Category Name, Deuce Shirt from Product name, 2013 from Year

then i want out like this

2422	2/16/2012	Baby Clothes	Aino Shoes	Halle Köln	Bob Park	41
2422	2/16/2012	Baby Clothes	Deuce Shirt	Halle Köln	Bob Park	11
4665	1/21/2013	Baby Clothes	Aino Shoes	La Moda d'il Futuri	Ricardo Gucci	61
4665	1/21/2013	Baby Clothes	Deuce Shirt	La Moda d'il Futuri	Ricardo Gucci	12

But output is not showing like this, it is showing selections related 572 rows some thing

Give some suggestions to out from this situation

Thank you in advance

Sudhakar