Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with the application number, the date of registration of the application, the date of closing the application, the status of the application.
My master calendar looks like this:
mainTable:
LOAD
[reference number],
[date of registration],
[closing date],
[status]
FROM [lib://..../test.xlsx]
(ooxml, embedded labels, table is Sheet1);
QuartersMap:
MAPPING LOAD
rowno() as Month,
Ceil (rowno()/3) & 'Q' as Quarter
AUTOGENERATE (12);
Temp:
Load
date(min(FieldValue('closing date', RecNo()))) as minDate,
date(max(FieldValue('closing date', RecNo()))) as maxDate
Resident mainTable;
Let varMinDate = Num(Floor(FieldValue('minDate', 1)));
Let varMaxDate = Num(Floor(FieldValue('maxDate', 1)));
drop Table Temp;
TempCalendar:
Load
$(varMinDate) + IterNo() - 1 as Num,
Date($(varMinDate)) + IterNo() - 1 as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() - 1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate as [closing date],
Week(TempDate) as Week_close,
Year(TempDate) as Year_close,
Month(TempDate) as Month_close,
day(TempDate) as Day_close
Resident TempCalendar
Order by TempDate asc;
drop Table TempCalendar
It is made by the closing date of the request.
My problem problem is this: I need to select a date interval in the Date Picker object. I choose dates from 04.03 to 12.03. The result is the following output:
How to make so that those requests whose date is less than the selected dates are also displayed? Since these requests are in progress at the moment, but they also need to be taken into account.
That is the output should be like this:
Sorry For the count error Last time,
Try this:- Count({< >+<status={'in work'},[closing date],[date of registration]={"<=$(=Max([closing date]))"},[reference number]-=p({<[closing date]={">=$(=Date(0))"}>})>} distinct [reference number])
This is the graph :-
and this is output table:-
Wow! Thank you so much, Akash! You helped me a lot! Your expression is just top notch! That's just what I needed.
Can I ask you an extra question. How did you get the table at the end like on your second screenshot?
Those are normal Dimension except rowno and the expression which are measures, I have sorted the table in ascending order on area basis.