Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jasonrimkus
Contributor II
Contributor II

Multiple P() and E() and/or

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.

 




 

 

 

 

Labels (6)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
jasonrimkus
Contributor II
Contributor II
Author

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

sunny_talwar

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)
jasonrimkus
Contributor II
Contributor II
Author

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.  

sunny_talwar

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