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

Straight Table-Sum of Rows divided by Expression Total

jjohnstonti618 — Tue, 25 Apr 2017 19:51:52 GMT

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

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

swuehl — Tue, 25 Apr 2017 20:58:38 GMT

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.

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

jjohnstonti618 — Tue, 25 Apr 2017 21:33:25 GMT

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.