topic Re: set expression to combine data from another row in QlikView

set expression to combine data from another row

daveatkins — Tue, 08 Oct 2019 16:13:23 GMT

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)

Re: set expression to combine data from another row

sunny_talwar — Tue, 08 Oct 2019 16:57:14 GMT

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

Re: set expression to combine data from another row

rwunderlich — Tue, 08 Oct 2019 17:02:45 GMT

The "TOTAL" keyword is your friend here:

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

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

Re: set expression to combine data from another row

daveatkins — Tue, 08 Oct 2019 18:44:05 GMT

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

Re: set expression to combine data from another row

daveatkins — Tue, 08 Oct 2019 20:29:18 GMT

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 )

Re: set expression to combine data from another row

daveatkins — Tue, 08 Oct 2019 20:51:38 GMT

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

Re: set expression to combine data from another row

rwunderlich — Tue, 08 Oct 2019 21:43:20 GMT

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/NestedAggregations/sum-of-rows-pivot-tables.htm

-Rob