Set Analysis comparing two date fields

[wlabarca99]

Experts,

I have two tables which are connected by a primary key called SKU.  Table 1 has daily sales data for each SKU and Table 2 has daily advertising spend for each SKU.  Both tables also have separate date fields and their basic structures are outlined at the bottom...

My goal is to use set analysis to get the sum of unit sales IF the OrderDate = AdvDate so that if i create a filter using the AdvDate field, my sales data will filter to those dates as well.

Here is all I have so far but it is not working:

SUM({<[ord_dt.autoCalendar.Date]=[adv_dt.autoCalendar.Date]>}qt_ordd)

TABLE1:

OrderDate	SKU	Unit Sales
9/1/2019	ABCDENIM1	2
9/2/2019	ABCDENIM1	5
9/3/2019	ABCDENIM1	3

TABLE2:

AdvDate	SKU	Ad Spend
9/1/2019	ABCDENIM1	$500
9/2/2019	ABCDENIM1	$600
9/3/2019	ABCDENIM1	$200

Thanks!

[treysmithdev]

Set Analysis is evaluated at the hypercube level, and limits what data is available. What this means is there is no dimensionality within it. So you could do:

SUM({<[ord_dt.autoCalendar.Date]=P([adv_dt.autoCalendar.Date])>}qt_ordd)

However, that would give you data where the OrderDate is available in all AdvDates. It doesn't happen record by record.

To do what you want, you will have to use a Sum If.

SUM(If([ord_dt.autoCalendar.Date]=[adv_dt.autoCalendar.Date],qt_ordd))

It is almost unanimous in the community to always resort to Set Analysis instead of SumIf, but this is one of the few scenarios where this is your only option (on the front end).

Your alternative, is to create a flag in the script.

AdvFlag_map:
Load
    SKU&'|'&Num(Floor(AdvDate)) as input,
    1
From
    [%advertising source%];


Orders:
Load
    SKU,
    OrderDate,
    qt_ordd,
    ApplyMap('AdvFlag_map',SKU&'|'&Num(Floor(OrderDate)),Null()) as _AdvFlag,
    ...
From
    [%order source%];

Advertising:
Load
    SKU,
    AdvDate,
    ...
From 
    [%advertising source];

Then do:

Sum({<_AdvFlag = {1}>} qt_ordd)