Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

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
Highlighted
Partner
Partner

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

That worked - thank you!!

3 Replies

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

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])
Highlighted
Partner
Partner

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

That worked - thank you!!

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

Super 🙂