Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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:

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

TABLE1:

OrderDateSKUUnit Sales
9/1/2019ABCDENIM12
9/2/2019ABCDENIM15
9/3/2019ABCDENIM13

 

TABLE2:

AdvDateSKUAd Spend
9/1/2019ABCDENIM1$500
9/2/2019ABCDENIM1$600
9/3/2019ABCDENIM1$200

 

Thanks!

Labels (3)
1 Reply
Highlighted
Luminary
Luminary

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)

 

Blog: WhereClause   Twitter: @treysmithdev