Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
This thread should help
Ignore all selections except some specific fields using Set Analysis
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
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.
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