Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mjtaft2017
Partner - Creator
Partner - Creator

Set Analysis built before/after July 1 of year in dimension

The requirement - of the items sold in a given month/year, give a count of those built before July 1 of that year and those built on/after July 1 of that year.  (Sold Date and Built Date are identified on common calendar by Date Type).  So in my set analysis I am looking at the BuiltDate of the records of DateType ='Sold'. The Fact table contains the itemKey, Sales Code, Built Date, Sold Date, BuiltDateNullFlag  (for those items sold but built prior to 2017)

Dimension is MonthYear (see table below).   This worked correctly with hardcoded values, but now I have 2 years of data 2017, 2018 and I want the year to be the year in the dimension.  When the MonthYear is  Aug-2017, I want to get the count of items sold in Aug-2017 that were built before July 1 2017 and those built on/after July 1 2017.   I don't want the user to have to select a year .   

 

Before July 1 :

COUNT({<DateType={'Sold'},[Sales Code]={'ABC'},BuiltDate={"<2018-07-01"}>+<DateType={'Sold'},[Sales Code]={'ABC'},BuiltDateNullFlag={'1'}>}DISTINCT [itemKey])

On/After July 1:

COUNT({<DateType={'Sold'},[Sales Code]={'RSK'},BuiltDate={">=2018-07-01"}>}DISTINCT [itemKey])

Set.PNG

Labels (2)
1 Solution

Accepted Solutions
mjtaft2017
Partner - Creator
Partner - Creator
Author

That worked - thank you!!

View solution in original post

3 Replies
sunny_talwar

Why don't you create a new field which is just a Month (and/or MonthNum) field and then use that. For example if you create a field like this

LOAD BuiltDate,
   Num(Month(BuiltDate)) as BuiltMonthNum
...
FROM ....

and then use this

Before July 1 :

Count({<DateType = {'Sold'}, [Sales Code] = {'ABC'}, BuiltMonthNum = {"<7"}>+<DateType = {'Sold'}, [Sales Code] ={'ABC'}, BuiltDateNullFlag = {'1'}>} DISTINCT [itemKey])

On/After July 1:

Count({<DateType = {'Sold'}, [Sales Code] = {'RSK'}, BuiltMonthNum = {">=7"}>} DISTINCT [itemKey])
mjtaft2017
Partner - Creator
Partner - Creator
Author

That worked - thank you!!

sunny_talwar

Super 🙂