Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
SR2
Contributor III
Contributor III

SET analysis with date comparision

Hello People,

I have my measures written as below in a Table (for all 12 months separately). I want to use Date dimension (End of month for last 12 months) created using Calendar field to create following measure.

How can I achieve following  using set analysis ?

COUNT(
{<
[Invoice Hold Count] -= {0}
,[Payment Method] = {'Direct Debit'}
,[Invoice Hold Date] = {"<=$(=MonthEnd(AddMonths(today(),-12)))"}
,[Invoice Release Date] = {">$(=MonthEnd(AddMonths(today(),-12)))"}
>}
[Invoice Number])

If I simply replace the date logic in expression above with Date field then it gives me null result. 

Logic I used to create Calendar - 

Let varMinDate = Num(Makedate(Year(Today())-1,Month(Today()),Day(Today())));
Let varMaxDate = Num(Makedate(Year(today()),Month(Today()),Day(Today())));

Datefield:
LOAD MonthEnd(date($(varMinDate)+IterNo()-1)) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Calender:
LOAD
Datefield AS Date,
Year(Datefield) AS Year,
Month(Datefield) as Month,
Day(Datefield) AS Day,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter

RESIDENT Datefield;

DROP TABLE Datefield;

Highly appreciate your help!

Thanks,

Labels (2)
0 Replies