Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Syntax for comparing two date fields as a OR statement not working

I would like to perform the following simple SQL or condition within  Qlik:

SELECT Count(1)

FROM  FactTable

WHERE  (SaleDate BETWEEN 20160501  AND 20160531

OR    [QuoteDate] BETWEEN 20160501 AND 20160531)


i.e simple or condition. I am using the inbuilt calendar liked to SaleDate so this calls for set Analysis.  However I am missing a couple of records due to the "or" statement failing:

RowNoQuoteDateSaleDate
12016043020160507
22016051320160609
32016052720160621

If I base it upon my logic on the Quote date (equal to MAY 2016) I miss the first row which should be included as the sale date is in MAY. If I base my logic on the Sale date I miss rows 2 and 3 which  should be included as the quote took place in MAY hence the OR.

What I have in Qlikview is:

=Count ({$< YearMonth =  {"$(=Date(QuoteDate,'YYYY-MM'))"},Year=,FiscalYear=,Month=,WeekDay=,Quarter= > +
<
YearMonth =  {"$(=Date(SaleDate,'YYYY-MM' ))"},Year=,FiscalYear=,Month=,WeekDay=,Quarter=>} IsSale

YearMonth is  2016-05 created from the selected year and month. No other date selections are made or available. By the way I have converted the dates to proper dates in the loads script. Sumof ISsale are Sales, Count of IsSale are quotes.

Simply stated  what I want is to include all quotes where the QuoteDate OR the saleDate match the selected (YearMonth e.g. ‘2016-05’) ignoring all date related sections so I am left with the full set of date related data to make the comparison .

Many Thanks in Advance & Kind Regards

Mike

1 Solution

Accepted Solutions
rubenmarin

Hi Mike, this expression returns 87:

=Count({<QuoteDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >  +

         <SaleDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >} IsSale)

In your date fields you have different date formats, some are in DD/MM/YYYY format. Try to keep a consistent date format.

View solution in original post

12 Replies
rubenmarin

Hi Mike, I think you should apply set analisys to the fields you want to filter based on selections, this is QuoteDate and SaleDate, so:

=Count ({$< QuoteDate={"$(=Date(Max(YearMonth),'YYYYMM'))*"}, + Year=,FiscalYear=,Month=,WeekDay=,Quarter= > +
< SaleDate
=  {"$(=Date(Max(YearMonth),'YYYYMM'))*"},

Year=,FiscalYear=,Month=,WeekDay=,Quarter=>} IsSale )


I also added an '*' to select all days within the max selected month, this may need be changed based on the format of the dates.


Also check this post: it gives hints on how to deal with different fact dates

Not applicable
Author

Hi Ruben,

Thank you for your prompt response and link to canonical dates.  There were was typo which I correct the first ‘+’  before the year=

Upon removing this I was receiving too many records i.e. the Date filter was not working.  So I removed the two '*'  from the expression as follows:

=Sum ({$< QuoteDate=  {"$(=Date     (Max(YearMonth),'YYYYMM'))"}, Year=,FiscalYear=,Month=,WeekDay=,Quarter= > +
<
SaleDate=  {"$(=QuoteDate(Max(YearMonth),'YYYYMM'))"}, Year=,FiscalYear=,Month=,WeekDay=,Quarter=>} IsSale)

but then I received zero records.

I think the BOTH the QuoteDate and SaleDate need  to be expressed as Date(SaleDate,’YYYY-MM’)  for an apples to apples comparison so to speak but then I get a syntax error? Any further ideas please?

Kind Regards

Mike 

rubenmarin

Hi Mike, yes, the plus sign was a typo.

I tried with this data:

RowNoQuoteDateSaleDate

1

2016043020160507
22016051320160609
32016052720160621
42016041520160425

I also added an independent master calendar.

If I apply my previuos expression (removing the plus sign) returns '3' when I select 2016-05, and '2' if I select 2016-04.

Also note your last expression has someting weird in the SaleDate set analysis:

=Sum ({$< QuoteDate=  {"$(=Date     (Max(YearMonth),'YYYYMM'))"},Year=,FiscalYear=,Month=,WeekDay=,Quarter= > +
<
SaleDate=  {"$(=QuoteDate(Max(YearMonth),'YYYYMM'))"},Year=,FiscalYear=,Month=,WeekDay=,Quarter=>} IsSale)

This should be the function Date(), QuoteDate() should return an error.

Not applicable
Author

Hi Ruben,


Sorry I should have stated from the onset the Sum works Count is incorrect. i.e. Sum = Sales, Count=Quotes.  I have now created the following expression based upon two calendars (sales and Quote) as follows:

//Predefined dates used instead of conventional Min to Max 1900.. lol
  LET vDateMin = Floor(MakeDate(2004,1,1));
LET vDateMax = Floor(Today());

[Master Calendar]:
LOAD *,
 
SaleYear & [SaleMonth (#)]                                      as [SalePeriod (#)],
 
AutoNumber(SaleYear & [SaleMonth (#)], 'MonthID')         as SaleMonthID,
 
SaleMonth & ' - ' & SaleYear                                    as SalePeriod,
'Q' &
Ceil([SaleMonth (#)] / 3)                               as SaleQuarter,
AutoNumber(SaleYear & 'Q' & Ceil([SaleMonth (#)] / 3), 'QuarterID') as SaleQuarterID;
LOAD
 
Date($(vDateMin) + IterNo() - 1, '$(DateFormat)')          as SaleDate,
 
Day($(vDateMin) + IterNo() - 1)                                  as [SaleDay (#)],
 
WeekDay($(vDateMin) + IterNo() - 1)                              as SaleWeekDay,
 
Year($(vDateMin) + IterNo() - 1)                                 as SaleYear,
 
Month($(vDateMin) + IterNo() - 1)                                as SaleMonth,
 
Num(Month($(vDateMin) + IterNo() - 1), '00')               as [SaleMonth (#)],
WeekYear(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)')) & '-' &
Num(Week(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)')), '00')     as [SaleYearWeek],
Date(MonthStart($(vDateMin) + IterNo() - 1), 'YYYY-MM') as SaleYearMonth,        
Dual(year(addmonths(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)'), 1-$(vFirstMonth))) &
'/' &
right(year(addmonths(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)'), 1-$(vFirstMonth)))+1,2),
year(addmonths(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)'), 1-$(vFirstMonth)))) as SaleFiscalYear
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

REM Now clear the variables;

LET vDateMin =;
LET vDateMax =;

I have select from the Year and Month on the SalesDate calendar. Then used the following expressions which gives an error but computes…

=Count({$< "$(=Date(SaleDate,'YYYY-MM')) " = {"$(=SaleDate((SaleYearMonth),'YYYYMM'))"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter= > +
<
"$(=Date(QuoteDate,'YYYY-MM'))" = {"$(=SaleDate((SaleYearMonth),'YYYYMM'))"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter= >} IsSale)

Interestingly both parts of this or expression give the same the same results so clearly it is NOT acting as an or statement at all, I am missing  the last 2 records below (would be many more if not filtered for testing).   Although the logic should dictate otherwise.  Can you see an “obvious” problem please? 

QuoteDate

SaleDate

QuoteMonthOnly

SaleMonthOnly

20160430

20160507

4

5

20160513

20160609

5

6

20160527

20160621

5

6

Note the sales month is June BUT belongs to the May as the quote originated in May hence  the OR requirement.

Many Thanks

Mike

rubenmarin

Hi Mike, I don't think that expression will work, at least the set analysis part, set analysis tell QV wich filters apply to wich fields:

{<Field={Value/s}>}

You can use a $-expanded expression as Field name, but it should return a field name.

"$(=Date(SaleDate,'YYYY-MM')")" --> This most probably is returning null, as there are different values for SalesDate and set analysis is calculated for the whole table (not row-by-row). This is why I used Max(YearMonth), to force it to return only one value.


Can you upload a sample to work on it? And an expected result so it will be easier for me to understand the desired result.

Not applicable
Author

How do I  upload when it's not a video or image (the only options I have) ?

oknotsen
Master III
Master III

Click on advanced editor (top right).

Click on attach (bottom right).

May you live in interesting times!
Not applicable
Author

rubenmarin

Hi Mike, this expression returns 87:

=Count({<QuoteDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >  +

         <SaleDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"}, SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >} IsSale)

In your date fields you have different date formats, some are in DD/MM/YYYY format. Try to keep a consistent date format.