topic Re: Set Analysis in App Development

Set Analysis

Husky_Bab12 — Mon, 29 Aug 2022 13:35:36 GMT

Hi ,
The basic calculation:
((Sum(Quantity)*(partPieceWeight))+((Sum(Quantity)*(shippingUnitTareWeight))/packageQuantity))*0.453592

I'm trying to use the below expression,

=round((((sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={"1"},%ReleaseSupplier = ,%Conveyance =>} Quantity)*partPieceWeight))

+
((sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} Quantity)*(shippingUnitTareWeight/packageQuantity))))*0.453592)

The first part is summing the quantity then multiplying with part weight for the production status 1.

The second part is summing the quantity and then multiplying with (shippingunit/packagequantity) for the production status 1.

The above expression works when I'm on particular part for one store.

When I'm looking for the same part with different stores it is showing -

Can someone help me with this?

Re: Set Analysis

Or — Mon, 29 Aug 2022 13:40:57 GMT

Generally speaking, if you want your set analysis to impact the entire expression, you need the entire expression to be contained within the aggregation functions. In your current expression, this is not the case as partPieceWeight, shippingUnitTareWeight, and packageQuantity are all outside the aggregation functions, which means they are not impacted by set analysis and will also return null if there is more than one possible value or if there are no possible values for any of them.

Re: Set Analysis

Husky_Bab12 — Mon, 29 Aug 2022 13:50:11 GMT

if i include them my results are overestimated.

example for a part to two different store is including for two stores is 23 .

for one store is 12

The approach I made similar to you said

=(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} Quantity*partPieceWeight))

+
(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} (Quantity*(shippingUnitTareWeight/packageQuantity)))

showing me 46.

Re: Set Analysis

Or — Mon, 29 Aug 2022 13:58:50 GMT

Since I can't see your data or data structure, I can't actually help you with this issue - it sounds like you have a duplication / one to many situation for these fields which is causing both the duplication and the return of null without the aggregation. I'd suggest you investigate that by placing each section of your code into a different expression/measure and then figuring out which one(s) don't return a single value for your rows.

Re: Set Analysis

Husky_Bab12 — Mon, 29 Aug 2022 15:19:33 GMT

Hi @Or ,

I checked with the data. It does not have any duplicates.

But I noticed with the below expression,

=(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={"1"},%ReleaseSupplier = ,%Conveyance =>} Quantity*partPieceWeight))

when the quantity is 7371 and partpieceweight is 0.12,

Expected value is 831

But actual is 1789.

I want to sum the quantity and then multiply with partpieceweight.

Re: Set Analysis

Or — Mon, 29 Aug 2022 16:32:27 GMT

If the quantity is in fact 7371, and the weight is in fact 0.12, and there is no duplication, then you should get a result of 7371*0.12. If you do not, that suggests that one of the above is not correct - either there is duplication, or the weight is not actually 0.12 for all rows, or the quantity is not 7371. As I suggested before, you should try and isolate the issue by summing the quantity separately, and getting an Only(partPieceWeight) with all of the set analysis added to confirm that weight is in fact 0.12 for all cases. If both of those are the case, I'd suggest checking for cases where the weight for a specific row is null (since null wouldn't break the only().

Another alternative approach is to add all of the fields from your fact table as table dimensions, and then trying to see if there are any cases where things aren't as expected.