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

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

Wed, 25 Nov 2020 16:16:04 GMT

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:

RowNo	QuoteDate	SaleDate
1	20160430	20160507
2	20160513	20160609
3	20160527	20160621

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

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

rubenmarin — Fri, 01 Jul 2016 12:45:20 GMT

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

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

Mon, 04 Jul 2016 08:57:19 GMT

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

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

rubenmarin — Mon, 04 Jul 2016 10:07:56 GMT

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

I tried with this data:

RowNo	QuoteDate	SaleDate
1	20160430	20160507
2	20160513	20160609
3	20160527	20160621
4	20160415	20160425

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.

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

Mon, 04 Jul 2016 11:34:23 GMT

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

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

rubenmarin — Mon, 04 Jul 2016 11:48:42 GMT

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.

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

Mon, 04 Jul 2016 15:43:40 GMT

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

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

oknotsen — Mon, 04 Jul 2016 15:48:55 GMT

Click on advanced editor (top right).

Click on attach (bottom right).

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

Tue, 05 Jul 2016 08:03:46 GMT

AgreementID	QuoteDate	SaleDate	QuoteMonthOnly	SaleMonthOnly
9705688	20160430	20160507	4	5
9744735	20160513	20160609	5	6
9790634	20160527	20160621	5	6

Note the bottom two values are missing but should be included as the quote was in May although the sale took place in June as 'simple' function. SaleYearMonth and QuoteYearMonth hold the date as YYYY-MM. The listbox New Set Totals shows 85 when May is selected but it should be 87 if the the OR function is working correctly. If the form is cleared and s search for ID 9790623 is made then the Master Cal Tabs will show the full date attributes.

Many Thanks

Mike

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

rubenmarin — Tue, 05 Jul 2016 09:37:30 GMT

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.

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

mjtaft2017 — Fri, 26 Oct 2018 20:13:55 GMT

Ruben -

I don't understand what the -* is for on the end of a couple of the statements just before the }

=Count({<QuoteDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"},

SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= > +

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

Can you explain what that is doing?

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

rubenmarin — Sun, 28 Oct 2018 20:28:13 GMT

Hi Mary, that's the string used to search, and the '*' is a wildcard to look for all combitanion of characters, ie: using "2018-10-*", filters dates starting by '2018-10-', wich will be all dates for october 2018.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Text_search_and_numeric_search.htm

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

viradia123 — Wed, 22 Jul 2020 20:20:56 GMT

I am comparing this way. Am I doing wrong?

Count({<[FIELD_NAME_1] = {'VALUE1'}, [DATE_FILED_1] -= {'[DATE_FILED_1] <= [DATE_FILED_12])'}>} COUNT_FIElD_NAME)