Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

set expression to combine data from another row

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)

 

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

The "TOTAL" keyword is your friend here:

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

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

View solution in original post

6 Replies
Highlighted

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

Highlighted
MVP & Luminary
MVP & Luminary

The "TOTAL" keyword is your friend here:

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

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

View solution in original post

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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 )

Highlighted
Partner
Partner

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

Highlighted
MVP & Luminary
MVP & Luminary

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