topic Re: Showing Open and closed work in each month in Connectivity & Data Prep

Showing Open and closed work in each month

Abhi_WP — Thu, 25 Jan 2024 05:24:31 GMT

Hi,

I have a request from user to show a graph which will have Year Month and for each Year Month I need to show how many work orders were opened and also show how many were closed (irrespective of when they were opened). The field I have in my table is Raised Date, Closed Date and Work order Status for Closed and Open. How can I achieve it ?

select
wod.word_no,
wod.rsd_dt,
wod.clsd_dt,
wod.word_stat_cde,
wod.word_cmptd_cde,
from
ndwu.work_order_dim wod,

Re: Showing Open and closed work in each month

Vegar — Thu, 25 Jan 2024 05:55:35 GMT

What if you make a date link table to your master calendar?

date_link:

Load

wod.word_no,

wod.clsd_dt as dt,

'Closed' as dt_type

Resident data;

Comcatenate Load

wod.word_no,

wod.rsd_dt as dt,

'Raised' as dt_type

Resident data;

Create a master calendar linked to the new dt field.

Then you can use yearmonth from your master calendar and dt_type as a dimension to distinguish between raised and closed dates.

Re: Showing Open and closed work in each month

Vegar — Thu, 25 Jan 2024 05:58:52 GMT

This old blog post, Canonical Date, by @hic could also be a good read in your situation.

Re: Showing Open and closed work in each month

Abhi_WP — Mon, 29 Jan 2024 03:34:59 GMT

Thank you very much for providing the link to the solution. It is very well explained there.