IF statement to Set Analysis

[ellenblackwell]

Trying to convert the following IF statement into set analysis. Green, bolded components of formula are the ones I am struggling with getting into working set analysis formula. Any help would be greatly appreciated. Thanks!

Ellen

=count (distinct if(FLG_ABC_Captured='Y' and FLG_XYZ_Captured_CMPTV='Y' and [ABC GEO ID]=[XYZ GEO ID] and [Price1]>[Price2], [POC ID]))

[Anonymous]

Hi Ellen,

Why you want to use set analysis,

As you are doing row by row comparison if statement is correct approach to do it.  [Price1]>[Price2] ...

[jonathandienst · Accepted solution]

Hi

=count ({<FLG_ABC_Captured={'Y'}, FLG_XYZ_Captured_CMPTV={'Y'}>} distinct if([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, [POC ID]))

You can't do the other two in a set expression - this is a row by row comparison of two fields, but the set expression is calculated once for the chart/table, not once per row.

The only way to avoid the Count(If()) here is to create a flag in the load script - something like

     LOAD

          ...

          If([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, 1, 0) As Flag1,

          ...

And now add a Flag1 = {1} clause to the set expression.

HTH

Jonathan

[ellenblackwell]

Thank you for the accurate, more elegant approach. I have many formulas that look like the one in my original request. The table containing the formulas loads excruciatingly slowly.

I also want to create a total in a Text Box object. Without the additional flag you mentioned, I will have to use the same formula you updated, correct?

Thank you!

[jonathandienst]

In the text box, the Flag option will work without change, but the Count(If()) will need to be in an aggr function for the row by row comparisons. Something like

     Sum(Aggr(Count({<FLG_ABC_Captured={'Y'}, FLG_XYZ_Captured_CMPTV={'Y'}>} distinct if([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, [POC ID])), dim1, dim2))  //check brackets...

where dim1, dim2 is a comma separated list of the same dimension(s) in your table (or at least return unique values for the row by row compared fields)

And the expression will take as long to evaluate as the table...