topic Re: Expression not working in Subtotals in QlikView

Expression not working in Subtotals

Tue, 10 Jun 2014 16:16:51 GMT

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

Re: Expression not working in Subtotals

venkatg6759 — Tue, 10 Jun 2014 16:25:00 GMT

Can your share your sample data.

Re: Expression not working in Subtotals

Tue, 10 Jun 2014 19:20:03 GMT

Here you go...

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

Re: Expression not working in Subtotals

hic — Tue, 10 Jun 2014 22:22:45 GMT

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

Re: Expression not working in Subtotals

Tue, 10 Jun 2014 23:08:34 GMT

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.

Re: Expression not working in Subtotals

shawn-qv — Wed, 11 Jun 2014 02:02:51 GMT

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.

Re: Expression not working in Subtotals

hic — Wed, 11 Jun 2014 09:08:38 GMT

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

Re: Expression not working in Subtotals

shawn-qv — Wed, 11 Jun 2014 23:08:13 GMT

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.