Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Valued Contributor

Can't seem to get the correct list using alternate states for a basket analysis?


I've got a relatively simple app running a basket analysis - it's got 4 tables: products, customers, sales and date.

The question I'm playing around with is "Which customers bought Product A but not B, B but not A and which bought both A and B".
I created an alternate state "Basket1" so that the client can choose two products from the same field (PRODUCT_DESCRIPTION) and the aim is to then to answer those questions.

The problem I'm running in to is with the dates - I allow it to be limited to selected years and months so the sales should then be limited to that range, meaning the customers that have or haven't bought the product will also change. This is done in the default state - I'm passing the values to the alternate state, and some of the expressions work correctly, but I can't get the final one to! It works fine when there is NO date selected, but I think I've got the syntax wrong around using the dates in the last expression.

For those buying A not B I have: Count({$-Basket1}Distinct CustomerID)

For those buying both I have: Count({$<CustomerID=P({Basket1}CustomerID)>}Distinct CustomerID)

These seem to work fine with the date because the date is in the default state. However when I try to apply this through in the alternate state it doesn't seem to calculate correctly:

For those buying B not A: Count({Basket1<CustomerID=e({$}CustomerID * p({Basket1<Year=$::Year, Month=$::Month>}CustomerID>}Distinct CustomerID)

Any help would be hugely appreciated!


Community Browser