Weekly report closed and open WO based on services and date.

[akmaximo83]

Dear all,

I want weekly report based on data. One sheet consist of data and the other sheet consists of report.
I want to pull data datewise open and close WOs.
For service - Electrical date is 21/10/2023 if Status is not COMP THEN WOs will be open based on report date. ( match servicw with report service)
2. if status is comp then date in weekly report match with actfinish then WOs to be deemed closed.
can any body assist me or write formulas.
File Attached Here.

 

[p45cal]

1. Are the values in the following picture correct for your data?

2. What version of Excel are you using?
3. The sheet PLUSPWO_CMMS.CTJVCAMP@T_1698157 looks like it might be a text or csv file loaded into Excel. It would be safer and probably easier to get Excel to process that file directly; could you attach that file (not an Excel file) here?
4. See table at cell S2 in the attached.

 

[akmaximo83]

Sir,
This is a CSV file. I am not able to get only the date part for filtering from the date timestamp.Please see the attched.

 

[p45cal]

Sir,
This is a CSV file. I am not able to get only the date part for filtering from the date timestamp.Please see the attched.

I'll be having a look at this, but it shouldn't be a problem.
In the meantime, could you answer questions 1 and 2?

Edit:
I see also there is no services column in the csv file, do I use commoditygroup instead? (The values in that column can be replaced with more friendly terms should you want to).

 

[akmaximo83]

Yes

 

[p45cal]

Yes

and:

In the meantime, could you answer questions 1 and 2?

 

[akmaximo83]

1. correct.
Excel version 2309

 

[p45cal]

In the attached, in cell A1 is the path and name to the csv file on your system. Currently it's showing where the file is on my system. Adjust this A1 cell to point to your file, then you can refresh the pivot table at A3 by either

• clicking on the Refresh All button in the Queries & Connections section of the Data tab of the ribbon
• or right-clicking somewhere in the pivot table and choosing Refresh.

Behind the scenes, cell A1 is a named range (pathAndFile) which Power Query uses to import the data in that csv file and Power Query transforms that data and pushes it out to the pivot table. You can filter the pivot to a single week by filtering column A (cell A5).

If you wanted, we could add a short macro to allow you to browse for the csv and refresh the query.


or