I would like to be able to accomplish this within a qlik sense table, not the load script if possible.
I have two tables with this general structure.
Orders: Primary Key OID
| OID(string) |
customerID (string) |
| 1|1 |
acmenorth |
| 1|2 |
apertureeast |
items: Primary Key itemID
| itemID (string) |
OID (string) |
inputDate (string) |
| 1|1 |
1|1 |
20211214 |
| 1|2 |
1|2 |
20211210
|
| 2|1 |
1|1 |
20211109
|
An order will always have one or more items, so a one to many relationship. Due to the implicit foreign key relationship, I have a synthetic key using OID to relate the tables in qlik sense.
I would like to create a table that subtracts the list of distinct customerIDs in the current month's items vs the previous month. In this case, December vs November. My overall goal is to be able to see what customerIDs have items in the current month that weren't in the previous month.
If I were to do this in sql to this example dataset, I would expect only apertureeast to be returned, as the other customer had an item in November.
I've tried two different methods.
1.
Measure:
count( {$<customerID= E({1<[inputDate.autoCalendar.YearMonth]={'2021-Nov'}>}), [inputDate.autoCalendar.YearMonth]={'2021-Dec'}>} [itemID])
Dimension:
customerID
2.
Measure:
count( {<[inputDate.autoCalendar.YearMonth]={'2021-Dec'}> - <[inputDate.autoCalendar.YearMonth]={'2021-Oct'}> } itemID)
Dimension:
customerID
Neither seem to be getting me what I want. In the load script I've set the DateFormat='YYYYMMDD'. I've used the autocalendar pre-generated script to convert it into the ym field that is seen in my examples. I've tried using inputDate = {'202112*'} for example, but I've found using the autocalendar field tends to work better.
Any suggestions would be appreciated! Thank you.
