topic AGGR and SET ANALYSIS in QlikView

AGGR and SET ANALYSIS

raajeshn — Fri, 29 Jun 2012 03:31:48 GMT

Hi Experts,

I have scenario where I would like to compare the sale of a particular product with all Products of the category - I use the following expression

=AGGR (NODISTINCT SUM({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> } SALES), [PRODUCT_CATEGORY])

This fetches me the expected results when no Product / Product Category is selected. However, When I select a Product / Product Category, I am seeing the correct numbers only for the selected Product / Product Category. How do I ensure, that my results are not changed, when there is a selection happening in either of Product or Product Category.

Any help is highly appreciated.

Thanks & Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

Fri, 29 Jun 2012 03:45:05 GMT

Raajesh,

Try the following:

Create a straight table with product category and product as dimensions.

Expression as:

Sum({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}>} Sales)/
Sum(Total <PRODUCT_CATEGORY> { <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}>} Sales)

Regards,

Kiran Rokkam

Re: AGGR and SET ANALYSIS

raajeshn — Fri, 29 Jun 2012 03:50:09 GMT

Hi Kiran,

Thanks for the quick response. But, I guess - I have the same problem - I dont want the expression to listen to the selectionsb- it does here. Moreover, the numbers are not matching. Appreciate your help.

Thanks & Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

Fri, 29 Jun 2012 03:55:44 GMT

Raajesh:

Need more details to debug. Can you scramble the data and share you qvw or some screenshots?

Regards,

Kiran Rokkam.

Re: AGGR and SET ANALYSIS

raajeshn — Fri, 29 Jun 2012 03:57:51 GMT

Sure Kiran. But I would need some time for this - I will attach it before tomorrow. Thanks a lot for your support.

Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

jonathandienst — Fri, 29 Jun 2012 06:21:21 GMT

Raajesh

I assume the aggr expression is a calculated dimension. Does the chart expression also override the product/product category selections?

Jonathan

Re: AGGR and SET ANALYSIS

Fri, 29 Jun 2012 09:49:11 GMT

Try this

=AGGR (NODISTINCT SUM({ 1<QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> } SALES), [PRODUCT_CATEGORY])

Re: AGGR and SET ANALYSIS

raajeshn — Fri, 29 Jun 2012 17:49:02 GMT

Hi Yogananthan,

I have already tried this, and it also produces the same result. Appreciate your support.

Thanks & Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

raajeshn — Fri, 29 Jun 2012 17:59:38 GMT

Hi Jonathan,

Thanks for the response. I dont know what made you assume that the expression is a calculated dimension - but you are right, I am actually comparing the company's products with competitor's products and hence there is a calculated expression, which will be like this

=IF(COMPANY='MY_COMPANY', PRODUCT).

However, I am not understanding your second question. What is the chart expression you are referring here. I have two expressions in my chart, one showing the actual sales of the Product - second showing the sales of the competitor products. This is my first expression.

=SUM({1<QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, PRODUCT=, [PRODUCT_CATEGORY]=>}SALES)

And my second expression is as follows:

=AGGR (NODISTINCT SUM({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> } SALES), [PRODUCT_CATEGORY])

Both these expressions are working good when no product / product category is selected. However, once either of them are selected, the graph reacts accordingly - i.e, if a product is selected, the table has numbers only for the selected product and nulls in all other rows. If I am select a product category, then all the products of the category has numbers while the other rows return null values. The above issue is available only in the Second expression, while the first expression behaves as expected.

I am not sure I understood your question and that is the reason for this long explanation (not sure, if I still answered you). Sorry for that. Appreciate your support

Thanks & Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

jonathandienst — Sat, 30 Jun 2012 10:59:34 GMT

Raajesh

OK, you are overriding the selections in the expressions. So now you need to suppress the rows with nulls/zeros.

First check that the Suppress Zeros check box is ticked. If it still displays the rows, you have at least one more expression that is returning something other than zero or null (a blanks string will cause the row to display).

You can force this expression to return zero if the above expressions are zero by modifying the expression:

= If(Column(1) = 0, 0, third expression)

Also suppress null values for the calculated dimension.

If these suggestions don't solve the problem, I suggest you post your app or a sample illustrating the problem.

Hope that helps

Jonathan

Re: AGGR and SET ANALYSIS

raajeshn — Tue, 03 Jul 2012 12:09:10 GMT

Hi Jonthan,

Thanks for the response and sorry - could not manage time earlier to create this mock.

First - my problem is, I dont want the rows to be hidden - rather they should be seen and the chart should not listen to the filters.

Please find attached the mock, where I am showing data for the past 6 months (based on the max(month)). The chart shows correct numbers when nothing is selected. However, when we select either Product or Product Category - the expression listens only to this selection. For Ex, If am selecting P1 in the selected example for Company Products, it shows null value in P2's denominator.

FYI - Actually I am showing only Company Products in the filter (th other two - Competitor Products and Other Products are are just for reference)

Hi Kiran, sorry could not create the mock earlier. Please find it attached now

Appreciate your support.

Thanks & Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

raajeshn — Wed, 04 Jul 2012 22:25:07 GMT

Hi Jonathan / Kiran..

Sorry to bother you guys - buty any luck on this issue...? Thanks.

Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

swuehl — Wed, 04 Jul 2012 22:47:42 GMT

Maybe this for a start:

Use

=only({1}AGGR(NODISTINCT SUM({<MONTH_YEAR = {">=$(vTimeStart)<=$(vTimeEnd)"}, PRODUCT_CATEGORY=, PRODUCT=> }SALEAMOUNT), PRODUCT_CATEGORY))

as denominator expression.

A selection in PRODUCT or PRODUCT_CAT will not change your numbers.

See attached.

edit:

If you want to select also in your Competitor or Company Products, I think you also need to clear the COMPANY_FLAG in above set expression within the sum.

Re: AGGR and SET ANALYSIS

raajeshn — Thu, 05 Jul 2012 08:49:11 GMT

Hi Swuehi,

Thanks a ton - your expression is working like a charm except that it was listening to Product Selections. However, You had the answer for this as well, as company was also getting selected when we select the Product from the Filter. Hence, I included the COMPANY= in the set expression and it is working. Once again, thanks a ton.

one more request - If time permits, can you help me understand this ONLY Function here.I went through the help but I guess I need little more details on how its working with this AGGR Statement. Thanks.

Regards,

Raajesh N

Re: AGGR and SET ANALYSIS

swuehl — Thu, 05 Jul 2012 09:22:53 GMT

The Help says about the aggr() function:

...

By default, the aggregation function will aggregate over the set of possible records defined by the selection.

...

That's the reason why you first saw only records for your selected or possible products.

I think the HELP section about aggr() is incorrect in describing (at least in my version) that you can use a set expression within the aggr() to define another set:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

....

An alternative set of records can be defined by a Set Analysis expression.

....

I don't think this works as described. So I use an outer aggregation function to define the new set, which will determine the aggr() dimension values used (i.e. all). I chose only() aggregation function because I believe it's most appropriate here.

Re: AGGR and SET ANALYSIS

raajeshn — Thu, 05 Jul 2012 12:05:56 GMT

Thanks for the swift response. I understand the reason now. Thanks a ton.

Regards,

Raajesh N