topic Re: Set Analysis - outside of filter? in QlikView

Set Analysis - outside of filter?

Fri, 03 Apr 2015 14:38:35 GMT

So I'm summing sales of a unit and comparing it to their prior year's sales. I'm doing this in a text paragraph all concatenated together, it's the sort of thing my users love. Example (Orange is calculated off fields):

"Unit A had sales of $3,000 ytd for 1Q 2014. For the same period in 2013, Unit A had sales of $2,500. The percentage change from period to period was 20%."

I've declared a vMaxYear, vPrevYear, vMaxQtr to derive from the filter.

Num(sum({$<year = {$(vMaxYear)}, qtr = {'<=$(=vQtrMax)'} > } [Sales]),$'#,##0 ') = $3000

Num(sum({$<year = {$(vPrevYear)}, qtr = {'<=$(=vQtrMax)'} > } [Sales]),$'#,##0 ') = $3000

This is all predicated that my filters are simplistic. I can only choose 1 Unit, and the paragraph will always show the maximum year. I want to add other text that takes into account data "outside" of the filter. So in this case, I'm filtered to Unit A, but I want to compare Unit A's sales to that of Unit B, C, D, etc. I'd like to say

Unit A's sales of $3,000 was 25% of the total market which had reported sales of $12,000.

Is there a way to do a set analysis that looks outside of the filter parameters and takes into account the other Unit's? That way I can calculate the 25% & the $12,000 in my example above?

Re: Set Analysis - outside of filter?

ramoncova06 — Fri, 03 Apr 2015 17:08:28 GMT

This thread should help

Ignore all selections except some specific fields using Set Analysis

Re: Set Analysis - outside of filter?

sunny_talwar — Fri, 03 Apr 2015 17:14:01 GMT

There are numerous things you can do with set analysis. For instance, {1} will ignore any filters where as {<FieldName = >} will ignore any selection in FieldName. Further {1<FieldName = p(FieldName)>} would ignore all selections except the one made for FieldName. and then there are more sophisticated set analysis you can employ based on your need.

You will find a good guide on set analysis here: Set Analysis: syntaxes, examples

HTH

Best,

Sunny

Re: Set Analysis - outside of filter?

Fri, 03 Apr 2015 17:58:41 GMT

Thanks. I've found pretty much exactly what you stated through some more creative googling. I'm only a week into qlikview use and am amazed at the power of the set analysis features. Syntax is a bit trick for me, but I'll get there.

Re: Set Analysis - outside of filter?

sunny_talwar — Fri, 03 Apr 2015 18:06:11 GMT

I am sure you will. We were all new one day and had to go through the difficult time of learning the basics. Not to say that some time in, I amazed by the kind of things we can do with set analysis. Do look at the document available at the link I shared. It is the most comprehensive guide for learning set analysis I have came across.

Best,

Sunny