topic Set Analysis Issue with Aggr in QlikView

Set Analysis Issue with Aggr

kishorj1982 — Tue, 09 Feb 2016 22:02:26 GMT

Hello,

Please find attached QVW, Chart : fl met threshold is expected to show only those FinList meets the weekly sales Threshold set on the slider. I am trying it with Set analysis with below expression.

=sum({$<FinList={"=max(aggr(sum(sale),FinList,[Ad Week])) > $(vSales_FR)"}>} (sale))

I know I am using max function and it shows only those FinList with max weekly sales above threshold. However this is just to test if my set analysis is working properly.

Now, what I want is 1 -1 comparison where I can show only those rows in the Chart where the sales at weekly level @ FinList are greater than sales set at threshold.

Can you please help me achieving it OR suggest any alternative way to accomplish it?

note - ALL SALES is the table where we have just sum of sales with week dimension.

Thanks in advance,

Re: Set Analysis Issue with Aggr

swuehl — Tue, 09 Feb 2016 22:35:10 GMT

Maybe like attached, using a composite key for FinList and Ad Week

Re: Set Analysis Issue with Aggr

kishorj1982 — Wed, 10 Feb 2016 00:06:15 GMT

Thanks swuehl. Its working as expected. Just wondering if I have diff table with additional 2 dimensions with lower granularity than Week and Finlist - will it work as appropriate in straight table?

I think it should be as Set analysis always works at the Field Level and not at the expression level thats my understanding.

I will test that as well and come back.

Meanwhile if you have suggestion on this query please let me know.

Thanks a ton! This community is a wonderful place. Thanks again....

Re: Set Analysis Issue with Aggr

oknotsen — Wed, 10 Feb 2016 09:03:52 GMT

If your question is now answered, please flag the correct answer with Correct Answer.

If there is still something unclear about your question, please let us know what you need more help with .

Re: Set Analysis Issue with Aggr

swuehl — Wed, 10 Feb 2016 10:36:20 GMT

If you add more dimensions, this might result in issues with grain mismatch.

Have a look at

Pitfalls of the Aggr function

You may need to adapt the composite key and the advanced aggregation dimensions in the advanced search expression then.

Alternatively, you can also use advanced aggregation (without the need of a composite key) like

=sum( Aggr(If (Sum(sale) > $(vSales_FR), Sum(sale)),FinList, [Ad Week]))

You would also need to adapt the dimensions of the aggr() function when you change the chart dimensions to finer granularity.

Re: Set Analysis Issue with Aggr

kishorj1982 — Wed, 10 Feb 2016 15:35:47 GMT

Thanks Swuehl.

But I need to restrict data based on Finlist and Ad week level as my threshold is uniform at that level. In that case how I can manage it with the chart (straight Table) where we have Diff set of dimensions - maybe at lower granularity in this case. I really cant add additional dimensions in the composite key or aggr function as it will alter the definition of applying threshold.

Please help me out here.