Expression in Set Analysis

[derekjones]

Hi All

I'm scratching my head here why this isn't working and hoping someone can enlighten me on why its not working..

I have a sum calculation where I'm using set analysis to restrict the sum to the total stock on items with the same manufacturer part as the item being reported.

=SUM({$<ProductManufPart={$(=only(ProductManufPart))}>} TOTAL StockSIQty)

I've tried the above and other combinations i.e.

=SUM({$<ProductManufPart={'$(=only(ProductManufPart))'}>} TOTAL StockSIQty)

=SUM({$<ProductManufPart={=$(only(ProductManufPart))}>} TOTAL StockSIQty)

None seem to work, however when I actually put the manufacturer part instead of the expression i.e. only(ProductManufPart) for the first line is 'ABCD' then it works fine.

My thanks in advance for any suggestions.

Derek

[Anonymous]

The M in ProductmanufPart isn't capitalized, I don't know if this is going to solve your problem but it's a start.  Can you post a sample of your file so we can take a look?

[derekjones]

Thanks Kevin, just spotted that, it was a typo in writing it out, in the actual qvw it was correct, I've corrected the above to save any confusion.

[er_mohit]

Try this

Sum({<ProductManufPart = {"=$(=only(ProductManufPart))"}>}TOTAL StockSIQty)

[derekjones]

Example attached.

ItemA and ItemB both share ManufPart of 'ABCD', Column 'Item Stock' contains individual stocks of 10 and 5, I want 'ManufPart Stock' to have 15 on both ItemA and ItemB. I've included two additional columns of ManufPart using the expression =only(ProductManufPart) to show it does equate to 'ABCD' for both ItemA and ItemB and a further column showing if I hardcode 'ABCD' into the expression it does equate to 15.

Hope this helps.

[derekjones]

Thansk er.mohit, unfortunately didn't work. I've now included a test file that you can use to try alternatives.

[danielrozental · Accepted solution]

You're trying to use set analysis in the wrong way. Set Analysis will only get evaluated once for your objects, it does not get evaluated for every single row.

That's why it doesn't work, because only(ProductManufPart) will actually return a null value, because there is more than one possible value.

You need to do it like this sum(aggr(sum(total <ProductManufPart> StockSIQty),ProductManufPart,Item)).

[derekjones]

Thanks Daniel, I wasn't aware it didn't evaluate within set analysis.

That worked perfectly.

[CELAMBARASAN]

Hi,

Please check with this Sum(TOTAL<ProductManufPart> StockSIQty)

Aggr function - affects performance.

[danielrozental]

Yes, I agree, but your expression won't work because ProductManufPart is not a dimension in the table.