Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Straight table condition

Hi Folks,

I am new to QlikView and have been trying to find a solution for a problem with no success. Below is the scenario, hope someone can help or guide me in the right direction.

I have a list box of products and i have created two list boxes from it one of which is in Alternate state, Say Product1 and Product2(Product2 is in Alternate State). I need to select one product from each of the two list boxes and I want a straight table to display two things:

1. List of Companies which have bought both the products(Once this is acheived then the sales-amount and OrderIDs for the two respective products) and

2. Similarly List of companies which have bought Product1 but not Product2.

I need the straight table to display the name of the Company, OrderID and Sales-amount. I am capturing the selection in Product1 into a variable vGroup1 and similarly the second in vGroup2. Once I am successfully able to display the 'right' company names in the straight table then ill go deeper and display the sales-amount for each of the two products and their respective OrderIDs.

I have been able to display the both in a textbox via the following commands:

For point 1 = Concat({<ProductName={'$(vGroup1)'}> * <ProductName={'$(vGroup2)'}>}Customers.CompanyName, ', ')

For point 2 = Concat({<ProductName={'$(vGroup1)'}> - <ProductName={'$(vGroup2)'}>}Customers.CompanyName, ', ')

But i have tried multiple variations(including Enable Conditional) of the same formula to display it in Straight Table but all it displays is Companies which have bought Product1 or at other times some some different combinations.

Really appreciate everyones time and efforts....

Tags (2)
16 Replies
Not applicable

Re: Straight table condition

Just a clarification. I need two separate tables, ie one with common companies(Point1) and one which will list companies who have bought Product but not Product2(Point2)

elena888
Valued Contributor

Re: Straight table condition

Hi!

It would be easier for us if you attached a sample qvw

Thanks!


Not applicable

Re: Straight table condition

Sure,

Its the first time i am posting my query, so did not realise it. Here it goes

elena888
Valued Contributor

Re: Straight table condition

Hi,

sorry for delay!

I found a solution for point 2: for point 1... I need more time

Hope this helps you.

Not applicable

Re: Straight table condition

Thanks for your efforts Elena, i have tried using and_mode and realised the solution is very simple with and_mode. But now the problem is that the team does not want the solution using and_mode but with the logic i used earlier and now they want it at OrderId level ie it should display only products which were bought in the same OrderID.

Not applicable

Re: Straight table condition

And thanks for your answer, i am sure if i follow your expression, it will definitely lead me somewhere.

morganaaron
Valued Contributor

Re: Straight table condition

Hi Muntazir,

It's probably worth looking at element functions P() and E(). I created a similar application that had the same product list in two states, and by choosing products from each list would give the customers that bought only one, only the other or both.

In a straight table in the default state, with your customer as the dimension, the expression would then be:

=Sum({<Customer=P({State1}DropAccountCode)>}Sales)

That would give you all the customers sales values that bought both products and their sales value on the default product. For the other product (in State1) you'll want to do the same but add a second modifier like:

=Sum({<Customer=P({State1}DropAccountCode), Product=P({State1}Product)>}Sales)

Which should give you their sales for the product in State 1.

If you want to expand that to OrderID, add that in as a dimension and run through similar steps as above including it in sets to ensure it's across the same OrderID in both states.

Not applicable

Re: Straight table condition

Hi Aaron,

I did ponder using p() and e() in set analysis but thought it may not work. Now since you've pointed out that it worked in your case, will definitely try the same.

Thanks for your inputs.

Not applicable

Re: Straight table condition

Hi Aaron,

Could you please clarify what 'DropAccountCode' stands for in your expression.

Community Browser