Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am hoping this is an easy one that I am just over thinking.
I need to show the product of a calculated measure for the max value of a dimension for each group or program. I am thinking it needs to be aggr, but I am not sure how to pull out the max
Sum({$<[Parameter]={ Net"}, Type = {"Milestone"}>} Value) | Program | Gate Seq |
22.00 | 10 | 3 |
-87.00 | 10 | 4 |
-498.07 | 10 | 6 |
12.00 | 11 | 1 |
187.10 | 11 | 2 |
45.00 | 11 | 3 |
In this instance I want to show the value -498.07 for Program 10 and 45.00 for Program 11. Something like this:
Program | X |
10 | -498.07 |
11 | 45.00 |
What is the formula I need for the X column?
Thanks as always!!
Thanks @Aasir ....this was helpful as it got me headed in the right direction. With a bit more digging...this worked:
Aggr(Sum({$<[Parameter]={"Net"},[Gate Seq] = {"$(=MinString([Gate Seq]))"}, Type = {"Milestone"}>} Value),Program)
@data_RN Please use the below code in your Load script:
NoConcatenate
Temp:
Load fabs(Value) as Value,
Program, GateSeq
Inline [
Value,Program, GateSeq
22.00, 10, 3
-87.00, 10, 4
-498.07,10, 6
12.00, 11, 1
187.10, 11, 2
45.00, 11, 3
];
NoConcatenate
Temp1:
Load Max(GateSeq)&Program as Key,
Program
Resident Temp
group by Program;
Inner join (Temp1)
Load
GateSeq&Program as Key,
Value
Resident Temp;
Drop Table Temp;
Drop field Key from Temp1;
Exit Script;
If this resolves your issue, please like and accept it as a solution.
In your X column
Aggr(Only({$<[Parameter]={'Net'}, Type={'Milestone'}>} Value), Program)
Thanks @Aasir ....this was helpful as it got me headed in the right direction. With a bit more digging...this worked:
Aggr(Sum({$<[Parameter]={"Net"},[Gate Seq] = {"$(=MinString([Gate Seq]))"}, Type = {"Milestone"}>} Value),Program)