SET analysis with date comparision

SR2 — Wed, 17 Mar 2021 09:18:26 GMT

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,

topic SET analysis with date comparision in QlikView

SET analysis with date comparision