topic Re: Expression in Set Analysis in QlikView

Expression in Set Analysis

derekjones — Fri, 03 May 2013 12:05:07 GMT

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

Re: Expression in Set Analysis

Anonymous — Fri, 03 May 2013 12:16:11 GMT

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?

Re: Expression in Set Analysis

derekjones — Fri, 03 May 2013 12:17:14 GMT

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.

Re: Expression in Set Analysis

er_mohit — Fri, 03 May 2013 12:20:51 GMT

Try this

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

Re: Expression in Set Analysis

derekjones — Fri, 03 May 2013 12:33:05 GMT

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.

Re: Expression in Set Analysis

derekjones — Fri, 03 May 2013 12:35:18 GMT

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

Re: Expression in Set Analysis

danielrozental — Fri, 03 May 2013 12:46:20 GMT

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)).

Re: Expression in Set Analysis

derekjones — Fri, 03 May 2013 12:54:04 GMT

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

That worked perfectly.

Re: Expression in Set Analysis

CELAMBARASAN — Fri, 03 May 2013 13:22:21 GMT

Hi,

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

Aggr function - affects performance.

Re: Expression in Set Analysis

danielrozental — Fri, 03 May 2013 13:27:26 GMT

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

Re: Expression in Set Analysis

CELAMBARASAN — Fri, 03 May 2013 13:34:34 GMT

Did you checked? by hiding the column?

I meant after including that in dimension

Re: Expression in Set Analysis

CELAMBARASAN — Fri, 03 May 2013 13:41:26 GMT

Check the attached file

Re: Expression in Set Analysis

derekjones — Fri, 03 May 2013 13:49:57 GMT

Thanks, I could do that to save unnecssary aggregation

Re: Expression in Set Analysis

danielrozental — Fri, 03 May 2013 14:12:23 GMT

It's a good idea Celambarasan, thanks. It might not work if the 2nd dimension can take several values for the the first dimension which seems is not the case here.