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: 
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