Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Set Analysis comparing two date fields

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:

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!

Labels (3)

• ### Set Analysis

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:
1
From

Orders:
SKU,
OrderDate,
qt_ordd,
...
From
[%order source%];

SKU,
...
From

Then do:

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