How to display all records that match the date selection condition

[Dmitryromanov23]

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:

[Gabbar]

Create a new Column in load Script using:-
If(isnull("Closing Date"),"date of registration","closing date") as DateField;

and use this date to link to calendar.

[Dmitryromanov23]

Thanks, Gabbar

This helped to increase the number of entries! However, in this case, records with an early date still do not get in.

For example, in the last image there is an entry: the request number is 4, the registration date is 02.03.2023. And I choose the dates 04.03-12.03. But this entry should also be displayed. How can it be achieved?

Thanks in advance!

[Gabbar]

Try This then, create your normal calendar as you were doing 
and now use measure
Count({<Status-={'In Work'}>+<Status={'In Work'},"Closing Date"=,Day_Close=,Week_Close=,Month_Close=,Year_Close=>} distinct reference_number),

We free the In Work Status field of Calendar and it will Work Normally.

This worked from your sample data picture, If this Doesnt Work tell me, the next method will increase UI load time. 

[Dmitryromanov23]

Thank, Gabbar for helping!

It might be worth presenting the whole task.
I have a bar chart. Which counts the number of references by area by status. For example, I select the dates 20.03 to 24.03. The master calendar is created by the closing date. But the display on the chart is not correct. It only shows 2 records that have the status of 'in work' and have the closing date. And it should show 7 more records that had the status of 'in work' during this period

When I use your expression: Count({<Status-={'In Work'}>+<Status={'In Work'},"Closing Date"=,Day_Close=,Week_Close=,Month_Close=,Year_Close=>} distinct reference_number) --> the number of records with the status of 'in work' is counted incorrectly

For example: choose 18.03-20.03

Could you help solve this problem?

[Gabbar]

I am a little confused, so please clarify:-
Do you need date_of_registration less than closing date after selection filter, like if you select 20.03 - 24.03 then you dont want reference_number with date_of_registration >24.03.
If that is the case please use this:-

 Count({<Status-={'In Work'}>+<Status={'In Work'},"Closing Date"=,Day_Close=,Week_Close=,Month_Close=,Year_Close=,date_of_registration={"<=$(=max([Closing Date]))"}>} distinct reference_number)

where Closing Date is Datefield of calendar.

[Dmitryromanov23]

Yes, Akash. You got that right! 

I add your expression as a measure in my bar chart. But how can I make the bar chart display the correct number of hits for the selected period? (for example, 20.03-24.03). That is, the sum of the three areas should be obtained:
justified - 5
unfounded - 2
in work - 9

At this point I have a problem

[Kushal_Chawda]

@Dmitryromanov23 see the attched

[Gabbar]

The expression in my previous comments gives the expected result,
it exactly gives
justified - 5
unfounded - 2
in work - 9,
The expression is
 Count({<Status-={'In Work'}>+<Status={'In Work'},"Closing Date"=,Day_Close=,Week_Close=,Month_Close=,Year_Close=,date_of_registration={"<=$(=max([Closing Date]))"}>} distinct reference_number)

With this expression, you are freeing 'In Work Filter' from date and then putting it again by using less than equal to in [Closing Date] and Date_of_registration.

[Dmitryromanov23]

I'm really appreciate your help, Akash. Thanks for suggestions!

But unfortunately, all the same, the number of records with the status "in work" is not calculated quite correctly. In your screenshot also. The sum of "in work" statuses for the areas AA, BB, CC is 10. And it should be 9. (AA - 2, BB - 4, CC - 3). This is when you select dates 20.03 - 24.03