Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.
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
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.
How do I upload when it's not a video or image (the only options I have) ?
Click on advanced editor (top right).
Click on attach (bottom right).
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
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.