Set Analysis - What to use when having many dimensions (Ignore actual choices + Concat)

[#1]

Hello,

I am currently developing an application where we have a couple of different facts connected to eachother by using a link table. Those facts are:

• Sales
• Sales Budget / Forecast
• Customer creation (more of a factless fact)
• Product creation (more of a factless fact)
• Stock
• Order

As one could guess there are quite some different dimensions linked to each of those facts (6 tables with in total around 45 fields of interest)

• Date
• Customer
• Product
• ProductGroup
• Reseller
• SalesChannel

Where some dimensional data is connected to multiple facts, they aren't connected to all of them. But their are use-cases where multiple facts should be shown even though a dimension is chosen that one or more of those facts are not. I can see 2 ways to solve this using set-analysis:

• Either one put all dimensions in a set-analysis that should be ignored

{$<YearMonth={"22"}, Date=, Day=, Week=, Ignore1=, Ignore2=, Ignore3= >}

• One takes everything and only fill in the dimensions that should be taken from active choices

{1<YearMonth={"22"}, Reseller={"$(=concat(Reseller,'","'))"}>}

Now my question are:

• Am i missing any available options to take care of such situations?
• What is the most common way to handle this kind of situations.
• Does this depend on the amount of dimensional data (fields) should be included/ignored and how many unique values they might contain?

[#2]

There was a similar thread posted recently on the topic of ignoring many fields or just including the one. I think your two approaches are on the right track. As you can probably guess, ignoring every field can be a bit of a headache. Here's a link to that discussion: http://community.qlik.com/forums/t/40602.aspx

As to your examples, keep in mind that Concat() can be affected by selections in other fields. You probably want GetFieldSelections() instead, which will list all selected values in the field. Concat() will give you all possible values, which can be different depending on the circumstances.

[stevedark]

Hi there,

Thanks NMiller for the link to a very interesting thread.

The way I tend to deal with this is to create a number of variables with collections of ignored fields (particularly useful for date dimensions) which can then be used in set analysis.

For example setting this variable:

vIgnoreDates: OrderDate=,OrderMonth=,OrderYear=,OrderDay=,OrderQtr=,FutureOrder=

A typical set analysis expression then becomes:

sum({<$(vIgnoreDates),OrderPeriod={'$(vMaxPeriod)'}>}[Sales Amount])

This can be further simplified by nesting variables and having a set analysis variable:

vSACurrentPeriod: {<$(vIgnoreDates),OrderPeriod={'$(vMaxPeriod)'}>}

And then the expression above can become simply:

sum($(vSACurrentPeriod)[Sales Amount])

If further dimensions need to be added to the ignore list then the change can just be made in one place and it will filter down through to all relevant expressions.

Hope that makes sense and is useful.

Regards,
Steve