Set Analysis And Condition

[erric3210]

Hi All,

@MayilVahanan @sunny_talwar 

I'm learning Set Analysis and facing one issue in it.

This is the sample Data:

LOAD * INLINE [
Product, Flag, Value, Units
Eggs, Daily, 100, 40
Eggs, Weekly, 1000, 400
Soap, Daily, 250, 10
Soap, Weekly, 2500, 100
Milk, Daily, 250, 8
Milk, Weekly, 2500, 80
Curd, Daily, 140, 20
Curd, Weekly, 1400, 200
];

I need all those products which have Flag='Daily' and Maximim Value. The Maximum value for Flag Daily is '250' and if there are 2 same values
like I've in my Data Set '250' so for such cases I need to show the Product which has Max units.

2 conditions need to be fulfilled.
So, from the above table, the result should be:

Product, Flag, Value, Units
Soap, Daily, 250, 10

because it have max units '10'

I'm trying to achieve the result through Set Intersection.

See this:

Regards,

Eric

[Kushal_Chawda]

@erric3210  I don't think set analysis will work here but you can try below expression

=concat( aggr(if(only({<Flag={'Daily'}>}Value)*1e10 + Only({<Flag={'Daily'}>}Units) *1e4 =
max(total aggr(only({<Flag={'Daily'}>}Value)*1e10 + Only({<Flag={'Daily'}>}Units) *1e4, Product, Flag)), Product),
Product, Flag),',')

[erric3210]

Hi Kush,

Thanks for the reply. is there any chance to populate result using Set Analysis.

As per my knowledge, we can't use Possible() function inside if condition. I need to use that.

Also, why there is a need to multiply with number.

Regards,.

Eric

[edwin]

try this:

sum({<Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Units)

it means get product where units is equal to the maximum unit where value is equal to the maximum value.  if there is only one product with max value, then the maximum unit would be the one for that product.  in your example, if milk and soap both had 10 units they would both be selected

[edwin]

or for your concat:

=concat( distinct {<Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

[Kushal_Chawda]

@erric3210  As I said set analysis won't work here because set evaluates once per chart . So if any chance you are able to find the Max Units for max values but that Max Units will be applied to all the products no matter there is a max value or not. 

If you still want to use the set analysis then mix of script solution I can suggest but again there is limitation as you won't get calculation dynamically.

If you could share why you want to use possible then I would try to accommodate in if statement.

[edwin]

it is possible to use set analysis only using the expression i posted

[erric3210]

HI Edwin,

Thanks for the reply.

Your shared expression won't give desired output because it is bringing one value in the Set expression.

So, your set expression is populating value 10 (Units={'10'}) inside the expression. This will fail if other products have same 10 units. I want result based on both value and units.

Like this:

Concat(DISTINCT {<Units={'250'},Value={'10'}>}Product) or,

Concat(DISTINCT {<Units={'250'}>*<Value={'10'}>}Product)

Regards,

Eric

[erric3210]

Hi Kush,

Thanks for all the clarification and explaining the theoretical part of Set analysis.

It was very helpful. I'm trying to achieve result by modifying yours & Edwin solution.

Hope that works.

Regards,

Eric

[erric3210]

Hi @Kushal_Chawda @edwin ,

I modified both of your expressions and I'm achieving the desired output using this:

=CONCAT(DISTINCT {<Value={$(=MAX({$<Flag={'Daily'}>}Value))}>*<Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

This is considering both scenarios.

Please correct me if I'm wrong here and also let me know whether this is the feasible solution or not.

Regards,

Eric