topic Re: Multiple P() and E() and/or in QlikView

Multiple P() and E() and/or

jasonrimkus — Sat, 16 Nov 2024 19:26:17 GMT

I am trying to accomplish the following: count(AccountId) with Have/Have Not set analysis based on products.

The accountid can live in two separate tables, an Opportunity and Order table. Within the two tables are products; Opp.Product and Order.Product. The values of the Product fields are textually the same and are sent to separate variables, vSoldHave, vSoldHaveNot, vProposedHave, and vProposedHaveNot using getfieldselections()

The user can select (from a list box) a list of products where they HAVE or HAVE NOT the products listed in their respective tables

So, they could select HAVE Product 'A', 'B', and 'C' in the Opportunity Table, HAVE product 'E' in the Order Table, but NOT have Product 'D' in Opportunity or Order table; and the chart will populate with only the account GUID's that match that criteria. Or any combination, Have in Order, but not have in Order, Have in Opportunity, but not have in Order, etc.

Re: Multiple P() and E() and/or

jasonrimkus — Fri, 20 Dec 2019 13:49:21 GMT

@sunny_talwar any ideas??

I have the expressions working by only using a set of 2 p() and e()'s, but i'm looking to consolidate them all into 1 expression, if possible..

2 Excludes:

Count(
{<
Opp.Service = ,
Order.Service = ,
Account.accountid =
E({<
Order.Service = {"$(vSoldHaveNot)"}
, OrderProductsold = {'1'}
, Order.HDRCancelFlag = {'0'}
, OrderProduct.CancelFlag = {'0'}
, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveNotYear))"}
>})
* E({<
Opp.Service = {"$(vProposedHaveNot)"}
, opportunity.createdon_Date = {">=$(=AddYears(Today(), vHaveNotYear))"}
>})
>}
Distinct Account.accountid)

2 includes:

Count(
{<
Opp.Service = ,
Order.Service = ,
Account.accountid =
P({<
Order.Service = {"$(vSoldHave)"}
, OrderProduct.sold = {'1'}
, Order.HDRCancelFlag = {'0'}
, OrderProduct.CancelFlag = {'0'}
, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveYear))"}
>})
* P({<
Opp.Service = {"$(vProposedHave)"}
, opportunity.createdon_Date = {">=$(=AddYears(Today(), vHaveYear))"}

>})
>}
Distinct Account.accountid)

1 P() and 1 E()

Count(
{<
Opp.Service = ,
Order.Service = ,
Account.accountid =
E({<
Order.Service = {"$(vSoldHaveNot)"}
, OrderProduct.davey_sold = {'1'}
, Order.HDRCancelFlag = {'0'}
, OrderProduct.CancelFlag = {'0'}
, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveNotYear))"}
>})
* P({<
Opp.Service = {"$(vSoldHave)"}
, OrderProduct.davey_sold = {'1'}
, Order.HDRCancelFlag = {'0'}
, OrderProduct.CancelFlag = {'0'}
, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveYear))"}
>})
>}
Distinct Account.accountid)

Re: Multiple P() and E() and/or

sunny_talwar — Fri, 20 Dec 2019 13:58:01 GMT

I am not entirely sure I understand this without seeing an example, but try this may be

Count({<Opp.Service, Order.Service, Account.accountid = E({<Order.Service = {"$(vSoldHaveNot)"}, OrderProductsold = {'1'}, Order.HDRCancelFlag = {'0'}, OrderProduct.CancelFlag = {'0'}, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveNotYear))"}>}) * E({<Opp.Service = {"$(vProposedHaveNot)"}, opportunity.createdon_Date = {">=$(=AddYears(Today(), vHaveNotYear))"}>}) * P({<Order.Service = {"$(vSoldHave)"}, OrderProduct.sold = {'1'}, Order.HDRCancelFlag = {'0'}, OrderProduct.CancelFlag = {'0'}, OrderCompleteDate = {">=$(=AddYears(Today(), vHaveYear))"}>}) * P({<Opp.Service = {"$(vProposedHave)"}, opportunity.createdon_Date = {">=$(=AddYears(Today(), vHaveYear))"}>}) >} Distinct Account.accountid)

Re: Multiple P() and E() and/or

jasonrimkus — Fri, 20 Dec 2019 15:51:05 GMT

Thanks Sunny for the quick reply. Sorry i tagged you on this post, but you always seem to pull stuff out that i'd never think of.

Unfortunately, this did not work. When in pairs, it works just fine, but adding an additional pair nothing popped.

I guess this is now more of a syntaxical question. Can you use multiple P() and E()s. And does each P() and E() NEED to return the list of AccountIds in order for it to run? If you can use more than 2 P() and E()s; is there someway to do OR (+?) rather than the * (which i'm assuming represent AND)? I cant find any documentation about it.

I ran the expression through, and it didnt return the expected result, making the proper selections so it would pop.

Re: Multiple P() and E() and/or

sunny_talwar — Fri, 20 Dec 2019 16:14:35 GMT

You are right... * is AND and + is OR. So, you can do OR with a +.... and in the INTERSECTION expression that you have right now... you need to have an account pass all the conditions before it can show up...