set expression to combine data from another row

[daveatkins]

I have data which summarizes by facility and includes some values that are to be apportioned across the facilities. These are called "float dollars." So you might have

facility A, expense item X, $100

facility A, float dollars, $50

so the total for the facility might be:

facility A = DepartmentX sum of values + float = 100+50 = $150

I need to write a set expression that will be something like this, for the float component:

Sum( { <DepartmentType={'Float Dollars'} >} CalcTotal)

How do I get the expression to "reset" the DepartmentType so that this sum will be evaluated on the same line as DepartmentX?

In other words, the resulting table of data would look like this for Facility A:

Department, direct expense, float component, total expense

DepartmentX, 100, 50, 150

Float Dollars, 50,50, 100 (this is actually not meaningful sum, but that is what the data would display as if you just added the columns, because float is recorded in the data as another department. In the report, this department would be excluded from the dimension so as not to have this line appearing)

[sunny_talwar]

What is DepartmentX here? Is it coming from a field in your data? Can you share a better sample?

[rwunderlich · Accepted solution]

The "TOTAL" keyword is your friend here:

Sum( { <DepartmentType={'Float Dollars'} >} TOTAL CalcTotal)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[daveatkins]

THANK YOU YES! I had forgotten how that worked; exactly what I was looking for

[daveatkins]

a slight complexity...so there is also a joined column containing  a factor...and I cannot get this expression to quite work:

Sum( { <DepartmentType={'Float Dollars'} >} TOTAL <CorpDesc> (CalcTotalPay * FloatFactor) )

The following expression works, but fails to sum properly in the totals...i.e. it is correct in the pivot for each CorpDesc, but in the totals, it is not using the correct FloatFactor at the necessary row level of detail:

=Sum(CalcTotalPay) + if(isnull(min(FloatFactor)),0,min(FloatFactor)) * Sum( { <DepartmentType={'Float Dollars'} >} TOTAL <CorpDesc> CalcTotalPay )

[daveatkins]

sorry, I realize this is wrong track entirely. maybe the factor should just be a variable or something because it does break down into about 10 rules. like "add 50% of the float dollars for this day, in this facility to one department and then 25% to a different department..."

[rwunderlich]

You likely have a "sum of rows" problem that requires Aggr(). See if this explains it https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

-Rob