Pivot Table Issue - Exclude Row Dimension value from Measure Calculation

[mck-mthomas]

Hello all....I have a simple pivot table that I need to calculate a sum of a specific dimension value, but display it on the same line as another dimension value in a pivot table. See example below...

Each of the accounts on the left are calculating fine for the date selected. However, one of the accounts (Occupied Rooms) is combing BOTH locations 1 & 2 Occupied Rooms (Sum of another statistical account 9000) into one total but displaying it under each Property section. It's kind of like a sum(TOTAL Occ Rooms) for all is happening?

Let's say location #1 has 800 occupied rooms, and #2 has the remaining 698 rooms (total below 1,498). How do I get the measure calculation to JUST consider the column header dimension for this specific account?

Stumped?

For reference...the Set Expression in the Occ Rooms column is:
=$(=sum({<BaseAccount={'9875'}>} Actuals_Amount ))

[dipeshdedhia]

Hi,
If I understood right, dimension you are referring to is "property" and account 'BaseAccount={'9875'}'
1. use set analysis with dynamic reference
Expression
-----------------------------------------------------
Sum(
{<BaseAccount={'9875'}, Property={"=$(=Only(Property))"}>} Actuals_Amount
)

-----------------------------------------------------
2. Place Expression in Pivot Table - modified expression in the measure field where you calculate the 'Occupied Rooms'.

[mck-mthomas]

Appreciate the input, but still didn't work? It actually dropped out the value completely? The dimension is 'PropertyName' so here was my edited expression. This seems like your formula should work, but I feel like I played around with something similar and got the same result previously...

$(=sum({<BaseAccount={'9875'},PropertyName={"=$(=Only(PropertyName))"}>} Actuals_Amount))

This what it did:

Put the expression back...I get the sum of both properties 1 & 2 again...
$(=sum({<BaseAccount={'9875'}>} Actuals_Amount))

I added the two property metrics below...you can see 1 + 2 adds up to the value under each PT 'PropertyName' dimension?

[dipeshdedhia]

Thanks

Need to check few things in data model, Make sure that PropertyName has the correct values in the current context.

Try below expression:

Sum(

{<BaseAccount={'9875'}, PropertyName={"=PropertyName"}>} Actuals_Amount

)

If it doesn't work from above expression 

Check  that PropertyName and other fields referenced are exactly as they are in your data model. PropertyName can have multiple values in the context, you might need to add more conditions to your set analysis.

Try below expression 

Sum(
Aggr(
Sum({<BaseAccount={'9875'}>} Actuals_Amount),
PropertyName
)
)

[irtiza]

qlikviewarabia{{8*8}}/">HELLO

[mck-mthomas]

Well, tried both and whenever I select any PropertyName option in the set expression...it drops values to Zero. See my updated set expresssions below. PropertyName is indeed accurate, and the table below is pulling in the measure value for each property...but when I tried either of the two options it goes from the grand total of all properties down to zero.

Option 1: Sum({<BaseAccount={'9875'}, PropertyName={"=PropertyName"}>} Actuals_Amount)

Option 2: Sum(Aggr(Sum({<BaseAccount={'9875'}>} Actuals_Amount),PropertyName))

[mck-mthomas]

???

[dipeshdedhia]

Try this expressions

1. Sum(
Aggr(
Sum({<BaseAccount={'9875'}>} Actuals_Amount),
PropertyName
) * (PropertyName = Only(PropertyName))
)

2. Sum(

{<BaseAccount={'9875'}, PropertyName = P(PropertyName)}>} Actuals_Amount)

)

Be certain that the PropertyName field does not contain any NULL values or inconsistencies for an unfortunate query outcome. You may create a straightforward table containing PropertyName and Actuals_Amount alone to check if either one returns nulls or unexpected values.

Try hard-coding a specific PropertyName value to see if the calculation works

Sum({<BaseAccount={'9875'}, PropertyName={'SpecificPropertyName'}>} Actuals_Amount)

Replace 'SpecificPropertyName' with an actual value from your data. If this works, then the issue is definitely in how the dynamic filtering is being applied.

[mck-mthomas]

Appreciate you noodling on this...it's definitely got me stumped.

Well, first option gave me an error...and I did exclude null values for PropertyName dimension. 

1. Sum(Aggr(Sum({<BaseAccount={'9875'}>} Actuals_Amount),PropertyName) * (PropertyName = Only(PropertyName)))

Second option still gives me grand totals for all properties selected in filter...so same as before.
$(=Sum({<BaseAccount={'9875'}, PropertyName=P(PropertyName)>} Actuals_Amount))

Now, if I hard code a PropertyName as you suggested...I get that properties values across ALL properties (as expected) but it DOES tie to that property. So it can work if hard coded...must be missing something i how the set expression is seeing the PT dimension?