topic Re: Subtract two variables inside a set analysis in QlikView

Subtract two variables inside a set analysis

Mon, 01 Aug 2011 12:53:36 GMT

Hi all,

I'm trying to filter the content of a pivot table using a set analysis on a dimension, filtered by another dimension calculated on values obtained by the subtraction of two variables.

Example:

only({$<CustomeID={$(var_customed_id_selected)}-{$(var_customer_id_excluded)}>} Exhibition_edition)

Inside my pivot table I'd like to view customer's exhibition participation, but only for those customers selected minus customer excluded.

The set of customers excluded is obtained through another customer table not linked in the data model.

The pivot table is not filtered..

Any tips?

Thanks a lot,

Alessandro.

Subtract two variables inside a set analysis

SunilChauhan — Mon, 01 Aug 2011 12:57:23 GMT

this may help

only({$<CustomeID={$(var_customed_id_selected)}}>} Exhibition_edition)

only({$<CustomeID={{$(var_customer_id_excluded)}>} Exhibition_edition)

Subtract two variables inside a set analysis

Mon, 01 Aug 2011 13:08:30 GMT

Thank you,

unfortunately the result is the same, the pivot table is not filtered by the expression.

For info, the two variables contain thow set of customer ID calculated though "concat" function.

I don't understand if it is the "only" function not used in a correct way or the subtraction not possible between the two variables.

Subtract two variables inside a set analysis

SunilChauhan — Mon, 01 Aug 2011 13:14:05 GMT

sum({$<CustomeID={$(var_customed_id_selected)}}>}distinct Exhibition_edition)

sum({$<CustomeID={{$(var_customer_id_excluded)}>} distinct Exhibition_edition)

Subtract two variables inside a set analysis

Mon, 01 Aug 2011 13:22:50 GMT

Nothing yet

Subtract two variables inside a set analysis

lironbaram — Mon, 01 Aug 2011 13:32:26 GMT

hei

what is the dimension of the pivot table

i thimk you can use the E() and P() function in set analsys

But i need better understanding of the pivot table structre

if you can post some sample data it would be great

Subtract two variables inside a set analysis

SunilChauhan — Mon, 01 Aug 2011 13:34:08 GMT

there was syntax error in above.please try this

sum({$<CustomeID={$(var_customed_id_selected)}>}distinct Exhibition_edition)

sum({$<CustomeID={$($(var_customer_id_excluded)}>}distinct Exhibition_edition)

thanks

Sunil Chauhan

Subtract two variables inside a set analysis

Mon, 01 Aug 2011 13:37:04 GMT

I saw that and I've already wrote that in the correct way.

No success yet.

Subtract two variables inside a set analysis

Mon, 01 Aug 2011 13:50:08 GMT

Well: the dimensions of the pivot table are the name of the customer, the name of the exhibition (concatenated with the edition) of the exhibition to which he participated, city and country of the customer.

Among the expressions, the calculation about exhibitor or visitor status of the customer, based on a certain set of rules on his past participations.

The dimension on which I'd like to rule the set analysis is the exhibition edition to which the customer has participated.

The logic is the sequent:

- I set the first variable T1 with all customerdID acutally selected into the document, where CustomerID field is linked to all other data inside the data model;

- I set the second variable T2 with the CustomerID to be excluded: these ID belong to a different table not linked with the rest of data model.

So, I want to filter on edition dimension, CustomerID that have participated to an exhibition (T1) minus CustomerID excluded by a list-box apart (T2) e subtract T1- T2 on the dimension.

Re: Subtract two variables inside a set analysis

johnw — Mon, 01 Aug 2011 23:22:26 GMT

I think it's much simpler than all that. I think this would work, no variables required:

{<CustomerID-=TheOtherCustomerIDField>}

Assuming I got the syntax correct, that expression says "Give me the customer IDs that I selected but exclude the ones I selected in the other customer ID field." However, I have no idea what you're doing with the only() or the sum() or what you're actually trying to accomplish, so merely plugging that set expression in to your expressions may not do what you want it to do.

Re: Subtract two variables inside a set analysis

perumal_41 — Tue, 02 Aug 2011 03:53:06 GMT

only({$<CustomeID={$(=$(var_customed_id_selected)}-{$(var_customer_id_excluded))}>} Exhibition_edition). try this code

Re: Subtract two variables inside a set analysis

lironbaram — Tue, 02 Aug 2011 07:59:20 GMT

hei attach is an example

what i did is reloading the customer list twice

when you select customer in the list box on the right side

it will exclude then from the table

i am using the expression

{<CustomerID=-P(CustID2)>} which mean Exluding the Possible Client from CustID2

hope its helps you

Re: Subtract two variables inside a set analysis

Tue, 02 Aug 2011 08:43:02 GMT

Thank you John, I tried your solution without success.

I know what I have to do but I've got no clear how to do it.

I tried all the solutions posted here but no result yet.

If I have an idea or an improvement I'll surely post it.

Re: Subtract two variables inside a set analysis

Tue, 02 Aug 2011 14:50:50 GMT

Maybe I'm near the solution but I've got a question: inside my pivot table, if my expression (I changed the "only" with a "count") returns two possible values, '0' and '1', and I want to show only the '1's , how can I do that?

I checked the option "Suppresso zero-values" on Presentation folder of the expression with no result.

Re: Subtract two variables inside a set analysis

Wed, 03 Aug 2011 07:07:19 GMT

Any suggestion about deleting rows from a pivot table in which the value of my expression return 0?

Thanks,

Alessandro.

Re: Subtract two variables inside a set analysis

Thu, 04 Aug 2011 10:58:16 GMT

Hi all,

I found the solution to my problem, in this expression:

if(GetSelectedCount(exhibition_name_excluded)>0 or GetSelectedCount(exhibition_edition_excluded)>0,

count({$<CustomerID={$(var_CustomerID_selected)}-{$(var_CustomerID_excluded)}>} Exhibition_edition),'2')

"exhibition_name_excluded" and "exhibition_edition_excluded" are the fields through which the user can choose the exhibition name or the exhibition edition to exclude. These fields are linked to the CustomerID_excluded inside the data model.

Once the exclusion is choosen, in my pivot table I show only the exhibiton edition (Exhibition_edition) filtered by set analysis {$<CustomerID={$(var_CustomerID_selected)}-{$(var_CustomerID_excluded)}>}

Inside the variable "var_CustomerID_selected" there are all the possible CustomerID (selected through CONCAT function) available in the document.

Inside the variable "var_CustomerID_excluded" there are all the excluded CustomerID (selected through CONCAT function), linked in the data model to the exhibitions exluded by the user.

The solution is good but with 600.000 CustomerID in my DB, the subtraction between the two variables and the calculation of the pivot table are too heavy for the server (Intel Xeon E7330@2.82 GHZ, 2 processors).

Any suggestions about that?

I was thinking to eliminate the two variables and subtract the CustomeID and CustomerID_excluded directly inside the set analysis, something like this:

if(GetSelectedCount(exhibition_name_excluded)>0 or GetSelectedCount(exhibition_edition_excluded)>0,

count({$<CustomerID={$(Chr(39) & concat(distinct Chr(39) & CustomerID, Chr(39) & ',' ) & Chr(39)) - {(Chr(39) & concat(distinct Chr(39) & CustomerID_escl, Chr(39) & ',' ) & Chr(39)) Exhibition_edition),'2')

The syntax doesn't work. Any suggestions?

Subtract two variables inside a set analysis

SunilChauhan — Thu, 04 Aug 2011 11:54:08 GMT

prsesentation-> check on supress zero value

if( FieldName<>0,FieldName)

Subtract two variables inside a set analysis

Wed, 31 Aug 2011 13:20:25 GMT

Hi all,

the solution I described before works correctly but the performances decrease massively and the application is not usable.

I thought the problem was storing a great number of customerID into the two variables and then subtract them.

Anyone can suggest me an alternative solution through which I can subtract from the possibile values (not only the selected ones) of an ID inside the document the values of another ID (in this case those selected)?
I'd need to filter the data linked to the ID resulting from the previous subtraction, inside a pivot table.

Subtract two variables inside a set analysis

Fri, 02 Sep 2011 08:00:07 GMT

Any suggestions?

Actually my set analysis is:

if(GetSelectedCount(Id_Of_Exhibition_To_Exclude)>0 or GetSelectedCount(Id_Of_Edition_Of_Exhibition_To_Exclude)>0,

count({$<Customer_ID={$(VAR_Possible_Customer_ID)}-{$(VAR_CustomerID_To_Exclude)}>} EditionOfExhibition),'2')

The CustomerID to exclude are taken by a secondary table in the data model, not linked to the "cloud".

In this way, independently from the selection of the customerID inside the document, the user can select which Customer (Exhibition) to exclude, taken from another table and so not influenced by other selection.

It works but the subtraction between the two variables is too heavy as I sad in the previous posts.

I'm looking for an alternative set analysis with better performance.

Anyone?

Thank you.

Subtract two variables inside a set analysis

SunilChauhan — Fri, 02 Sep 2011 08:12:22 GMT

go to presentation and check supress zero values

If(expression>0,expression)

where expression is formula written

or clear mee if i am wrong

thanks

Sunil