Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
jjohnstonti618
New Contributor III

Straight Table-Sum of Rows divided by Expression Total

This is driving me insane.  Is there a simple way in a straight table to have an expression where you divide ONE expression total that is a "Sum of Rows" by a SECOND expression total that is simply an "Expression Total"? (referring to the Total Mode option in straight tables)  Line by line with all the dimensions shown, the calculations work out perfectly fine obviously, but when you get to the overall total at the bottom, it simply does not calculate the overall Variance % correctly. 

Note: Not sure if this is relevant but my 2 expressions in the above example are not just straight sums--- The first one is, hence why i utilize the default "Expression Total" in the total mode...but my second expression MUST utilize the "Sum of Rows" total mode due to the fact that its a RangeMax formula that takes either a different existing expression result OR a variable that is input by the user on dashboard.

Any ideas?

Thanks

2 Replies
MVP
MVP

Re: Straight Table-Sum of Rows divided by Expression Total

You can try to emulate the sum-of-rows using advanced aggregation, like

=Sum( Aggr( YourCurrentExpression, AllYourChartDimensionFields))

for example

=Sum(Aggr( Count(DISTINCT ID), Customer, Country))

Then you can use expression total total mode.

jjohnstonti618
New Contributor III

Re: Straight Table-Sum of Rows divided by Expression Total

Thank you for quick reply.  This mode will indeed work.  I was utilizing this same sum(aggr( approach on earlier expressions in my table already; HOWEVER, i did not realize that you cannot utilize a relabeled expression " in this sum(aggr( formula..or atleast that appears to have been my issue cuz after i renamed it, it works fine now.

For example, i pull in a field called Sales from the script load and load it as an expression formula sum(aggr( Sales, Dim1, Dim2, etc))...but then i re-label it as "Super Sales" in the straight table.  THEN I try to perform a sum(aggr( on Super Sales (blue text) in a new expression and it doesnt work--i just get all zeros.  I had to switch it back to Sales (maroon text).  Anyway, Im rambling now.  Thanks for the help.