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