Set Analysis - How to pass a field value in set analysys

[rajuamet]

Hi Techies,

Here I have two tables:

Table1:

Snapshot_Month	Products_Aggregate
Dec	A,B,C,D,E
Nov	B,C,D
Oct	A,F

Table2:

Snapshot_Month	Products	Ratingsum
Dec	A	100
Dec	B	200
Dec	C	300
Dec	D	400
Dec	E	100
Nov	A	100
Nov	B	200
Nov	C	300
Nov	D	400
Nov	E	100
Oct	A	100
Oct	B	200
Oct	C	300
Oct	D	400
Oct	E	100

In a straight table, Snapshot_month is the dimension, Count(Products) is the expression.

Here, I need to pass Products_Aggregate via set expression to filter the products.

For example:

This is how we need the set expression.

Snapshot_Month	Expression
Dec	Count({$<Product={'A,B,C,D,E'}>}Product)
Nov	Count({$<Product={'B,C,D'}>}Product)
Oct	Count({$<Product={'A,F'}>}Product)

Required Output:

Snapshot_Month	Output
Dec	5
Nov	3
Oct	2

Please let me know how to achieve this.

[rajuamet]

Hi Pradosh,

I can not count the Product_Aggregate. Because Products count may differ in real scenario. I need to pass the Product_Aggregate set to count the Product.

Thank you.

[rajuamet]

Hi Kaanerisen,

As mentioned above, I can not count the Product_Aggregate. Because Products count may differ in real scenario. I need to pass the Product_Aggregate set to count the Product.

Thank you.

[kaanerisen · Accepted solution]

OK then how about this 🙂

COUNT(IF(WildMatch(prodAgg,product&',*','*,'&product,'*,'&product&',*',product),product))

Your required output is mentioned in your post is

Snapshot_Month	Output
Dec	5
Nov	3
Oct	2

But Oct should be 1 because there is only Product A data that match in table 1 in October.

If you want to use set analysis, You should create a uniqueID for month&product.

load AutoNumber(month&'|'&product) as key,* Inline [
month,product,value
Dec,A,100
Dec,B,200
Dec,C,300
Dec,D,400
Dec,E,100
Nov,A,100
Nov,B,200
Nov,C,300
Nov,D,400
Nov,E,100
Oct,A,100
Oct,B,200
Oct,C,300
Oct,D,400
Oct,E,100
]

Count({<key={"=WildMatch(prodAgg,product&',*','*,'&product,'*,'&product&',*',product)"}>}key)

Hope it helps.