Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

AGGR and SET ANALYSIS

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

1 Solution

Accepted Solutions
MVP
MVP

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.

View solution in original post

15 Replies
Not applicable

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

Partner
Partner

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

Not applicable

Raajesh:

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

Regards,

Kiran Rokkam.

Partner
Partner

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

Raajesh

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

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Try this

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

Partner
Partner

Hi Yogananthan,

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

Thanks & Regards,

Raajesh N

Partner
Partner

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein