Expression not working in Subtotals

[#1]

I have a pivot table with 5 dimensions.  One measure is “Sales”, another is “Quota”, the third is “Sales % of Quota”  (Sales/Quota).  As you can probably see where I am going…the calculation at the furthest level/dimensionality is working as expected, but at the Subtotals of the other levels, the "Sales % of Quota" calculation is not.

I’ve tried messing around with the AGGR function in numerous forms, and have searched the forum, but have not yet found the correct solution.  Any suggestions?

Thanks!

Dan

[venkatg6759]

Can your share your sample data.

[#3]

Here you go...

http://community.qlik.com/servlet/JiveServlet/downloadBody/6670-102-1-8324/Sample%20Sales-Quota%20Da...

[hic]

If the first measure is Sum(Sales), the second is Sum(Quota) and the third is Sum(Sales)/Sum(Quota), it should work straight off. What do you use as expressions?

You should not need any Aggr() for this.

HIC

[#5]

  I'm using the following Expression...the "IF" statements are to eliminate managers from the [Full Name] dimension.  Also, the measure "SIM_Q" contains ONLY data for the current Quarter.

=sum

(if(ActDate >= Floor(QuarterStart(vLoadDate)) AND
[Full Name] <> [Team Manager] AND
[Full Name] <> [Region Manager] AND
[Full Name] <> [Division Manager], ActCount))
/
sum(if([Full Name] <> [Team Manager] AND
[Full Name] <> [Region Manager] AND
[Full Name] <> [Division Manager], SIM_Q))

Thanks.

[shawn-qv]

It's a little hard to tell what the issue is without understanding a little bit more about your data and their association. Perhaps, a screenshot or a sample would help.

S.

[hic]

Well your expression lacks an aggregation function in the first parameter of the if-function. That's why it cannot evaluate.

The field references (e.g. ActDate, ActCount, [Full Name], [Team Manager]) make sense if there is only one name, one manager etc. per dimensional value. But for the subtotals you have several, and then the expression cannot be evaluated.

It should work if you add an aggregation function, e.g.

Sum(if(ActDate >= Floor(QuarterStart(vLoadDate)) AND

[Full Name] <> [Team Manager] AND

[Full Name] <> [Region Manager] AND

[Full Name] <> [Division Manager], ActCount))

/

sum(if([Full Name] <> [Team Manager] AND

[Full Name] <> [Region Manager] AND

[Full Name] <> [Division Manager], SIM_Q))

See more on

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

HIC

[shawn-qv]

One possible suggestion is to populate and use a "manager flag" in the (master) data.

You could have one called [IsManager], and it need only be a 1 or 0.

That will allow you to construct your expression like:

sum({<[IsManager] = {0}>} ActCount) / sum({<[IsManager] = {0}>} SIM_Q)

NOTE: In your script, the criteria for [IsManager] can be customize to account for Team, Region and Division Manager before assigning a 1. If there are more manager types, then that can also be added to the criteria.

Sometimes, having just one [IsManager] flag may not be enough, in which case you may populate additional flags for easier set expressions in your charts.