Can I use a count distinct equation as the dimension in my set analysis?

[ryan24]

I am attempting to use an equation as the dimension in my set analysis where I am counting distinct accounts. 

As an example

=(COUNT(DISTINCT{<[PRODUCT TYPE]={'PRODUCT 1'}>} [ACCOUNT NUMBER])

This accurately gives me a distinct number of accounts that have purchased 'PRODUCT 1'

Additionally I have a Qlik Script set up that tells me a count of each of 5 product categories purchased... i.e. they purchased 3 types of products.  Shown below

If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0')

The above equation gives me a 0,1,2,3,4, or 5 if they have purchased 0,1,2,3,4, or 5 product types.

I am trying get count the distinct [ACCOUNT NUMBERS] that have purchased 3 product types. 

I tried replacing [PRODUCT TYPE]={'PRODUCT 1'} in the below equation with [Long equation I shared above]={'3'} but am receiving an error in expression.

=(COUNT(DISTINCT{<[PRODUCT TYPE]={'PRODUCT 1'}>} [ACCOUNT NUMBER]) 

Is it possible to replace the column [PRODUCT TYPE] with an equation, like I am attempting? 

The equation i've tried, which is not working looks like this: 

------------------------------------------------------------------------------------

(COUNT(DISTINCT{<If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0')={'1'}>}[MASTER ACCT NUMBER]))

THANK YOU FOR ANY HELP AND I HOPE THIS MAKES SENSE!!

[BrunPierre]

I'm not clear about this requirement, maybe try this to count each of 5 product categories purchased.

IF(Sum({<[PRODUCT TYPE]={"=Match(Subfield([PRODUCT TYPE],' ',2),'1','2','3','4','5')" }>}UNITS)>0,1,0)

[ryan24]

Thank you kindly for the reply, however I'm not sure what this formula is providing / calculating. 

I will attempt to clarify the need. I am wanting to calculate the number of accounts who have purchased a certain number of 5 different products. 

For example, what is the total  number of accounts who have purchased 3 of the 5 products. 

The below query tells me how many of the 5 products have been purchased, and when I used it as a dimension in a table by customer it tells how many of 5 products each individual customer has purchased.

The next step I need to give me a distinct count of how many accounts have purchased a certain number of the 5 products, ie how many accounts have purchased 3 of 5 products... how many have purchased 4 of the 5 products and so on. 

I have tried using the below formula within a (count(distinct formula by account however I have been unsuccessful.

I hope this clears up some confusion. 

------------------------------------------------------------------------

If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')

+

If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0')

[BrunPierre]

You can use the variable overview to create the set analysis expressions in a variable.

And then reference it for example

Count({<[PRODUCT TYPE]={"=$(vProductPurchased)"}>} DISTINCT [Account Number])

[ryan24]

In this example what would the Defintion/fx for the variable be?

In your example equation below I feel like I need a variable equation for "[PRODUCT TYPE]" portion of the equation, rather than the "=$(vProductPurchased)" right?

To me the  "=$(vProductPurchased)" could replaced with =3, =2, etc. 

It's the dimension/column [PRODUCT TYPE] that I do not have or need to replace in the equation

Count({<[PRODUCT TYPE]={"=$(vProductPurchased)"}>} DISTINCT [Account Number])

Thank you again. 

[marcus_sommer]

If I understand you right yo want to count a count what means to nest aggregations. This is possible but needs to apply an aggr() to be able to specify the dimensional context on which the outer aggregation should be performed.

This might be integrated into your approach but I think it could be simplified, maybe with something like:

aggr(
    COUNT({<[PRODUCT TYPE]=p([PRODUCT TYPE])>} DISTINCT [PRODUCT TYPE]),
[ACCOUNT NUMBER])

as a calculated dimension or with:

sum(aggr(
    -(COUNT({<[PRODUCT TYPE]=p([PRODUCT TYPE])>} DISTINCT [PRODUCT TYPE])>=3),
[ACCOUNT NUMBER]))

as an expression. Personally I would prefer the above showed calculated dimension because it allows complete dynamic views because the user could select the wanted products and from this dimension the appropriate number of product-types.

- Marcus

[ryan24]

Thank you Marcus. 

[PRODUCT TYPE] is not a field that I have, rather a score out of 5 I've calculated with the below equation

If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0') 

This equation give's me a score of 0-5 of each to see which products have been purchased. 

If a line item has been purchased a score of 1 is assigned for that line, if not 0, and so on for each line.

It's important to note that the [PRODUCT TYPE] field in the example above is actually multiple fields across a product hierarchy. ie [PRODUCT TYPE] in the first line may be [SUB FAMILY] and [PRODUCT TYPE] in the second line may be [FAMILY]. This scoring system allows me to determine a score of products purchased across multiple levels and is working as indented.  

When I use this equation in a table, by account number, it tells me the score 0-5 of each account. 

My issue now is trying to determine how many accounts have a score of 1 or 2 or 3 etc. 

I tried inserting this equation as the dimension within in a count(distinct equation by account number. However I am getting an error in expression. I'll label where I inserted the above scoring equation as "HERE" below. An example where I'm trying to find how many accounts have a score of 3.

(COUNT(DISTINCT{<"HERE"={'3'}>} [ACCOUNT NUMBER]))

[marcus_sommer]

I'm not sure if I could comprehend your data-model and the wanted views in regard to the statement that [PRODUCT TYPE] isn't a field else it's intended to query against a kind of a product-hierrachy. I think you need to explain your data-model and views more in detail and precise.

Nevertheless deducting your description you may apply something like this:

sum(aggr(
   -(rangesum(
         sign(sum({< [Family] = {'x'}>} Unit)), 
         sign(sum({< [Sub Family] = {'y'}>} Unit)),
         sign(sum({< [Product] = {'z'}>} Unit)),
         ...) >= 3),
Account))

Beside this such an approach is quite static and therefore I suggest to consider to make some changes / extensions within the data-model to simplify this task and to make it more dynamically. Using a product-hierrachy doesn't mean that you didn't could have such [Product Type] and/or further scoring information in parallel within your data-model. Maybe The As-Of Table - Qlik Community - 1466130 could be helpful to make such associated information accessible.

- Marcus 

[ryan24]

Thanks Marcus, 

This equation I shared (below)is working as intended for me: 

If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0') 

I'm just needing to add the account level to it count distinctly how many accounts have each score 0-5. 

In other words include the equation I've shared into a count(distinct with account number.

The equation you shared seems to total the units for each product category, whereas I am only trying figure out have the account has purchased any of the product. ie how my equation shows >0, score of 1... if not score 0. 

Is there a way to nest in my equation into a the dimension of the count distinct with out an error in expression. 

(COUNT(DISTINCT{<"HERE"={'3'}>} [ACCOUNT NUMBER]))

(COUNT(DISTINCT{<If(Sum({<[PRODUCT TYPE]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[PRODUCT TYPE]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0') ={'3'}>} [ACCOUNT NUMBER]))

[ryan24]

Solution: 

sum(if(Aggr(
If(Sum({<[SUB FAMILY]={'PRODUCT 1'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[FAMILY]={'PRODUCT 2'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[FAMILY]={'PRODUCT 3'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[FAMILY]={'PRODUCT 4'}>}[UNITS]) > 0, '1', '0')
+
If(Sum({<[FAMILY]={'PRODUCT 5'}>}[UNITS]) > 0, '1', '0'),[ACCOUNT NUMBER])=3,1))