Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Set analysis-Include excluded values

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.

1 Solution

Accepted Solutions
Not applicable

Re: Set analysis-Include excluded values

Hi

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

View solution in original post

14 Replies
Not applicable

Re: Set analysis-Include excluded values


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

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.

Not applicable

Re: Set analysis-Include excluded values

Hi shankar,

No it's not working

Not applicable

Re: Set analysis-Include excluded values

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

Partner
Partner

Re: Set analysis-Include excluded values

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.

MVP
MVP

Re: Set analysis-Include excluded values

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

Not applicable

Re: Set analysis-Include excluded values

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?

Partner
Partner

Re: Set analysis-Include excluded values

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...

Not applicable

Re: Set analysis-Include excluded values

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)