Count Attribute Instances instead of Projects

PJG

Hi everyone!

In our system, a project can have up to 9 go-live dates. I am comparing our 2023 planned go-lives to the actual go-lives, to determine if they were on-time (with a 25 day allowance)

First, I use this BM to determine if the go-live was in 2023:

case

when Year(Actual Business Go-Live 1 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 2 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 3 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 4 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 5 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 6 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 7 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 8 Date_p) = 2023 then 'Yes'

when Year(Actual Business Go-Live 9 Date_p) = 2023 then 'Yes'

else 'No'

end

Then I use this to determine if they were on-time

case

when Actual Business Go-Live 1 Date_p <= DATE_ADD(Initial Business Go-Live 1 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 2 Date_p <= DATE_ADD(Initial Business Go-Live 2 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 3 Date_p <= DATE_ADD(Initial Business Go-Live 3 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 4 Date_p <= DATE_ADD(Initial Business Go-Live 4 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 5 Date_p <= DATE_ADD(Initial Business Go-Live 5 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 6 Date_p <= DATE_ADD(Initial Business Go-Live 6 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 7 Date_p <= DATE_ADD(Initial Business Go-Live 7 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 8 Date_p <= DATE_ADD(Initial Business Go-Live 8 Date_p, interval 25 day) then 'On-Time'

when Actual Business Go-Live 9 Date_p <= DATE_ADD(Initial Business Go-Live 9 Date_p, interval 25 day) then 'On-Time'

else 'Delayed'

end

My problem is that my card is counting the projects and not the go-lives. There are a total of 33 projects, but 3 of them have 2 go-lives, so there are 36 go-lives in total. I'm only seeing 33 as the total in my card.

How can I correct this?

Thanks!

marcel_luthi

I might be wrong, so right now your Dataset would be pivoted, so in a single Row per project you have up to 9 possible Go-Live Dates (with Initial and Actual) for each. Something like:

There are 2 approaches I can think of for this:

Beast Mode approach:

One that will return the number of times it was completed within the given window, which would look something like this but with 6 more lines for the other possible dates:

CASE WHEN YEAR(Actual Date1)=2023 AND Actual Date1 <= DATE_ADD(Initial Date1,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date2`)=2023 AND `Actual Date2` <= DATE_ADD(`Initial Date2`,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date3`)=2023 AND `Actual Date3` <= DATE_ADD(`Initial Date3`,INTERVAL 25 DAY) THEN 1 ELSE 0 END

Then a best mode to tell you the number of the dates that fall in the period, regardless of whether or not they were completed in the 25 days period:

CASE WHEN YEAR(Actual Date1)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date2)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date3)=2023 THEN 1 ELSE 0 END

And in your graphs you can do a SUM of these fields to get the total you'd expect. You can make this more versatile by having the Year and the Interval be Variables rather than hardcoded, so you don't have to redo if someone later ask, what about things that we achieve in a 7 day window rather than 25, or what if I want to know for 2022 instead of 2023. This with the table explained above will yield the following results:

ETL approach to unpivot (overkill for this request IMHO)

Then your ETL would look something like this (I know there might be more efficient ways out there, but this should do the trick):

The Make Date Pairsjust concatenates the actual and initial date for each of your possible options.

In the Select Columns, we choose only the key project information we want to keep available for charts plus the Pairs we created earlier. In the Dynamic Unpivot you select all the columns except the pairs as values that will not be pivoted.

Then we split the columns again with the Split Columns to get separate value and we force them to be treated as Dates by setting up their type with Alter Columns

Optional: This approach will create one row for each date, so 9 rows per Project. You can apply a filter rows first to only keep the dates for which you have value pairs.

You'd build your card from this new dataset instead, and you'll only compare the number of times Actual Date is less than Initial Date.

Hope this helps,

Marcel Lüthi

ST_Superman

It's always helpful to have a small sample of the dataset to help understand the structure of the data. However, from your description of the fields, I'm assuming that your data may look something like this:

With each project having a single row of data and multiple columns of data that contain the different "go live" dates for that project.

What I think you will need to do is to use an ETL to convert your data to look more like this:

With one row of data per project, per GoLive number.

You could even then add calculations to the ETL to determine the Year for Actual Go Live and if the project was On Time.

This data structure will give you a separate count for each time a project has a Go Live that is on time versus delayed.

ST_Superman

@PJG What does your data look like? Do you have one row of data per project? Or one row per go live date? When you summarize your data, are you using Count? or Count Distinct?

PJG

Hi @ST_-Superman-_ , thanks for the reply.

I'm a bit confused by your question, but I'm 100% sure that's due to my own lack of knowledge :) Apologies if I don't provide exactly what you're asking for.

The data is one record = one project. Each project has 9 initial and 9 actual go-live dates as attributes, so it would be one row per go-live I suppose.

Where do I get to define count vs count distinct?

I'm using the "Percent of total" bar graph, so I only define the Bar Name and the Bar Value. The Bar Name is the second block of code I posted above, and the Bar Value is a Count of the same code.

GrantSmith

Have you thought about restructuring your data so that each record is a type of go-live?

ID | Go Live Number | Actual Go-Live | Initial Go-Live

Then you can filter on the Actual Go-lIve date value for 2023 or use the date filter for this year and perform a date diff between actual and initial to see if it's an on-time.

CASE WHEN Actual Go-Live <= DATE_ADD(`Initial Go-Live`, INTERVAL 25 DAY) THEN 'On-Time' ELSE 'Delayed' END

You can then count the number of records you have in your dataset to get the total number of go-lives instead of the number of projects.

PJG

Hi @GrantSmith that's not an option unfortunately, and doesn't fit with our processes or how we need to use the system.

marcel_luthi

I might be wrong, so right now your Dataset would be pivoted, so in a single Row per project you have up to 9 possible Go-Live Dates (with Initial and Actual) for each. Something like:

There are 2 approaches I can think of for this:

Beast Mode approach:

One that will return the number of times it was completed within the given window, which would look something like this but with 6 more lines for the other possible dates:

CASE WHEN YEAR(Actual Date1)=2023 AND Actual Date1 <= DATE_ADD(Initial Date1,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date2`)=2023 AND `Actual Date2` <= DATE_ADD(`Initial Date2`,INTERVAL 25 DAY) THEN 1 ELSE 0 END
+ CASE WHEN YEAR(`Actual Date3`)=2023 AND `Actual Date3` <= DATE_ADD(`Initial Date3`,INTERVAL 25 DAY) THEN 1 ELSE 0 END

Then a best mode to tell you the number of the dates that fall in the period, regardless of whether or not they were completed in the 25 days period:

CASE WHEN YEAR(Actual Date1)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date2)=2023 THEN 1 ELSE 0 END

+ CASE WHEN YEAR(Actual Date3)=2023 THEN 1 ELSE 0 END

And in your graphs you can do a SUM of these fields to get the total you'd expect. You can make this more versatile by having the Year and the Interval be Variables rather than hardcoded, so you don't have to redo if someone later ask, what about things that we achieve in a 7 day window rather than 25, or what if I want to know for 2022 instead of 2023. This with the table explained above will yield the following results:

ETL approach to unpivot (overkill for this request IMHO)

Then your ETL would look something like this (I know there might be more efficient ways out there, but this should do the trick):

The Make Date Pairsjust concatenates the actual and initial date for each of your possible options.

In the Select Columns, we choose only the key project information we want to keep available for charts plus the Pairs we created earlier. In the Dynamic Unpivot you select all the columns except the pairs as values that will not be pivoted.

Then we split the columns again with the Split Columns to get separate value and we force them to be treated as Dates by setting up their type with Alter Columns

Optional: This approach will create one row for each date, so 9 rows per Project. You can apply a filter rows first to only keep the dates for which you have value pairs.

You'd build your card from this new dataset instead, and you'll only compare the number of times Actual Date is less than Initial Date.

Hope this helps,

Marcel Lüthi

ST_Superman

It's always helpful to have a small sample of the dataset to help understand the structure of the data. However, from your description of the fields, I'm assuming that your data may look something like this:

With each project having a single row of data and multiple columns of data that contain the different "go live" dates for that project.

What I think you will need to do is to use an ETL to convert your data to look more like this:

With one row of data per project, per GoLive number.

You could even then add calculations to the ETL to determine the Year for Actual Go Live and if the project was On Time.

This data structure will give you a separate count for each time a project has a Go Live that is on time versus delayed.