topic Re: Reference another subset within aggregate function in QlikView

Reference another subset within aggregate function

Mon, 05 May 2014 22:39:24 GMT

Hello, I have what might seem to be a rather simple requirement, but being new to QV, have yet to find any good examples or clear documentation suggesting that QV can in fact do this:

Given the following set (13 months in descending order from May 2014 to May 2013)

Year, Month, Affiliate, CustomerType, OrderQuantity

2014, 5, US, Partner, 10

2014, 4, US, Partner, 9

2014, 3, US, Partner, 8

2014, 2, US, Partner, 7

2014, 1, US, Partner, 7

2013, 12, US, Partner, 7

2013, 11, US, Partner, 6

2013, 10, US, Partner, 9

2013, 9, US, Partner, 6

2013, 8, US, Partner, 5

2013, 7, US, Partner, 6

2013, 6, US, Partner, 5

2013, 5, US, Partner, 3

Display a pivot table with the following summary when the user selects/filters on the May 2014 record:

Period, Affiliate, CustomerType, OrderQuantity, OrderQuantityLastYear, %Change, YTD

May 2014, US, Partner, 10, 3, 333%, 41

Based on what I've read, to get the OrderQuantityLastYear, column, one would enter a set analysis expression like this:

Sum ({<Year={$(=Year-1)}, Month={$(=Month)}, ...>} OrderQuantity)

or even like this:

Sum ({<Year={2013}, Month={5}, ...>} OrderQuantity)

Where ... are the remaining columns that should be equal to the current record. But this does not have the effect of summarizing records with year 2013, month 5, and appending the result to the current record in a new column -- the output is always just 0. Am I doing something wrong here, or is this just not possible? Is seems so simple...

Any advice is much appreciated.

Re: Reference another subset within aggregate function

swuehl — Mon, 05 May 2014 22:44:01 GMT

If you are using dimensions Year and Month, the current dimension value is incompatible with your set.

You need to use TOTAL qualifier to make it compatible, then:

Sum (TOTAL {<Year={2013}, Month={5}, ...>} OrderQuantity)

Re: Reference another subset within aggregate function

Fri, 09 May 2014 18:30:39 GMT

So two requirements of this type of report would seem to be:

1. The ability to perform mathematical operations within a set expression. As none of the examples in the documentation show this, it may simply be unsupported.

2. The ability to reference the "current" record on the right-hand-side of an expression. The examples in the documentation that reference a dimension on the RHS, are referencing the entire set of values for that dimension, not the "current" value.

As it seems these things are not supported, I've moved the work done here to the database engine, however that has the undesirable qualities of being both inflexible (the user cannot easily change the report) and self-joins required to implement this in SQL can be taxing on the query engine. Any gurus out there able to show that these two capabilities are in fact supported in QV?

Re: Reference another subset within aggregate function

Fri, 09 May 2014 18:32:50 GMT

This response was helpful to me as a newbie, but it did not solve the stated problem.

Re: Re: Reference another subset within aggregate function

swuehl — Fri, 09 May 2014 18:49:05 GMT

Sorry if this haven't solved your issue, I think I haven't fully understood your points 1 and 2.

What about attached sample?

Re: Reference another subset within aggregate function

Mon, 12 May 2014 12:41:07 GMT

Sorry swuehl, I am running Personal Edition and am not able to open the attachment.

Re: Reference another subset within aggregate function

swuehl — Mon, 12 May 2014 20:30:41 GMT

I used the data from your original post, then created a straight table with dimensions Year, Month, Affiliate, Customer Type and some expressions:

=sum(OrderQuantity)

=Sum (TOTAL {<Year={$(=max(Year)-1)}, Month={$(=max(Month))} >} OrderQuantity)

=column(1)/column(2)

=sum({<Month=>}TOTAL OrderQuantity)

and selected Year = 2014 and Month = 5

Just to demo some thoughts.