Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having real trouble with the syntax of my formula. Currently in a table i have this:
whereby i have a list of projects and the number of days since their last steering status. 0 is a default for never having had one before.
The # Projects column is my expression to limit the contents of the table and the formula is this:
if(DaysSinceLast_SteeringStatusT =0 or DaysSinceLast_SteeringStatusT >45,
Count(aggr(COUNT(
{<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"},
>}
distinct [Project Code])
,Programme))
+
Count(aggr(COUNT(
{<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {0},
>}
distinct [Project Code])
,Programme)))
I have to aggr the count because i am rolling up child projects of xyz to L2 and 'Programme' is the level i aggregate at. I also have to include the IF statement because there are some stragglers left behind in the rollup when i use DaysSInceLast_SteeringStatusT.
What I am trying to do though is to put the number 41 that you see in the table into a KPI (text box) but I can't figure out how to as I cannot use the IF statement
if(DaysSinceLast_SteeringStatusT =0 or DaysSinceLast_SteeringStatusT >45,....
as it doesnt relate to anything like in the table.
I tried doubling up on the aggr functions outside of the IF statement (below) but it doesnt work.
=
count(
{<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}
>}
aggr(
if( DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT >45,
Count(aggr((COUNT(
{<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}
>}
distinct [Project Code]))
)
,Programme))
,Programme)
distinct [Project Code])
Any help translating the table formula into a formula that works in a text box would be great I'm struggling to get to grips wtih aggrs.
May be you need this
Count(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}>}
DISTINCT [Project Code])
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {0}>}
DISTINCT [Project Code]))
, Programme))
But I feel like you really need a Sum for the outer aggregation like this
Sum(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}>}
DISTINCT [Project Code])
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {0}>}
DISTINCT [Project Code]))
, Programme))
May be you need this
Count(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}>}
DISTINCT [Project Code])
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {0}>}
DISTINCT [Project Code]))
, Programme))
But I feel like you really need a Sum for the outer aggregation like this
Sum(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {">45"}>}
DISTINCT [Project Code])
Count({<
[L2 Programme Type Code Parent] = {'xyz'},
[Year] = {$(v_Max_Year)},
ReportMonth={$(vCurrentMonth)},
DaysSinceLast_SteeringStatusT = {0}>}
DISTINCT [Project Code]))
, Programme))