Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm using an alternate state and want to filter in set analysis to the possible customer ids (CID) for the selected field in the alternate state:
count({<CID=P({<Product=[ALT State]::Product>})>}distinct CID)
I need this to be at the CID level because the data is laid out as multiple rows for a customer with a product on each row. In my chart I want to see the count of customers for all products that have a customer with the product selected in the alternate state.
The above works perfect if a product is selected in the alternate state. If a product is not selected, I want to see the values for all customers. When a value is not selected from the alternate state, nothing is brought back in the set. Is there some way that I can do this without an if statement outside the set analysis to check on the count of selected products?
Thank you!
Josh
Hi Josh,
The issue is in a subtle difference between the two syntax formats:
Field = Set::Field
vs.
Field = P({Set} Field)
The first format refers to the selected values in the field. The second format refers to the possible values in the field. For your purpose, you need to use the second format to get all possible values of Products from the Alternate State, so your expression should look like this:
count({<CID=P({<Product=P({<[ALT State]>})>}) >} distinct CID)
I hope I didn't miss any brackets on the way, since I'm typing it here from memory...
To learn more advanced development techniques, including advanced Set Analysis, AGGR(), Alternate States, data modeling, performance, scripting, and more - join us at the Masters Summit for Qlik in New Orleans on November 14-16. This is a once-a-year event, not to be missed!
Cheers,
Hi Josh,
The issue is in a subtle difference between the two syntax formats:
Field = Set::Field
vs.
Field = P({Set} Field)
The first format refers to the selected values in the field. The second format refers to the possible values in the field. For your purpose, you need to use the second format to get all possible values of Products from the Alternate State, so your expression should look like this:
count({<CID=P({<Product=P({<[ALT State]>})>}) >} distinct CID)
I hope I didn't miss any brackets on the way, since I'm typing it here from memory...
To learn more advanced development techniques, including advanced Set Analysis, AGGR(), Alternate States, data modeling, performance, scripting, and more - join us at the Masters Summit for Qlik in New Orleans on November 14-16. This is a once-a-year event, not to be missed!
Cheers,
Brilliant, thank you so much!