Set analysis based on a dimension

[Rich-HHE]

I'm new to set analysis and have read dozens of examples online and I cannot quite figure out how to do the following. I have a separate table of reporting months, not related to the sales table on any field. I did this intentionally since I don't want to select sales data based on this dimension. However, maybe there is a way to do that I'm not seeing.

My Reporting Month Table

MONTH_BEGIN

2015-01-01

2015-02-01

2015-03-01

... and so on

For each reporting month, I need to know (product examples are made up of course):

1. How many customers purchased a SHOE within the prior 18 months
2. How many customers did not ever purchase a SHOE prior to the reporting month, AND did not ever purchase SOCKS prior to the reporting month, but did purchase LACES any time prior to the reporting month. Obviously ignoring what happened after each reporting month for that row only.

The resulting table would look like this:

MONTH_BEGIN	SHOE LAST 18 MONTHS	Never Shoe Prior, Never Socks Prior, Purchased Laces Prior
2015-01-01	7,544	18,495
2015-02-01	7,850	18,400
2015-03-01	7,900	18,359

Here is one of the set analysis expression I've been trying. But I think the set analysis doesn't work with an unrelated field.

=Count(DISTINCT {$<PRODUCT_GROUP={SHOE},AMOUNT_ORDER={">0"},MONTH_BEGIN={"<=$(=AddMonths(DATE_BEST,-18))"}>} CUSTOMER)

Thanks in advance for any suggestions.

Rich

[MK_QSL]

Set Analysis calculated once per chart, not per line. So you need to calculate this in script.

Provide sample data.

[Oleg_Troyansky · Accepted solution]

As Manish mentioned, Set Analysis cannot directly refer to the Dimension values because it's calculated only once per chart.

So, the trick is to create the desired database associations and generate conditional flags that can be perceived as "static" in Set Analysis. When it comes to dates, the commonly used solution is the "As of Date" table - you create a calendar with the "display" dates and then build a table that associates the "display" dates with the "transaction" dates using conditional flags.

For example: all the "transaction" dates that fall within the last month, compared to the "display" dates, will be marked with _LastMonth_Flag = 1 and all other transaction dates will have _LastMonth_Flag=0. Now, your Set Analysis expression is rather simple:

_LastMonth_Flag = {1}

You can read a more detailed description of the As of Date table in this blog article:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

Or, you can find even more details in my new book QlikView Your Business.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Rich-HHE]

Thank you for the link and the commonly used term for this situation.

[Rich-HHE]

Thank you for confirming my suspicion.