Another option would be to create a calculated dimension in a chart with this ...
So you have 3 dimensions (CustomerID, Agreementid and the above) then sum(AMT) as your expression. The calculated dimension sets to Null any value below 180, so you need to tick the box Suppress when Value is Null.
Please be aware that calculations in a dimension are inefficient compared to calculations in an expression, they take more time and resources.
If the selection is done in the dimension then there are 2 different places to consider for the end result:
- the limitation of result set in the dimension
- the calculation in the expression
Yes, that's a fair point. In fact, if it was me I would probably do this in script anyway by appending the max(DPD) value as a new column and referencing it in a normal expression (apologies if anyone else has already suggested this in their attached examples).
load * inline [
CustomerID, Agreementid, DPD, AMT
100, C01, 20, 100
100, A02, 30, 200
100, B04, 10, 300
200, X101, 180, 500
200, Y102, 30, 200
200, Z104, 60, 60
300, M105, 60, 400
300, N106, 190, 500
300, O107, 20, 300 ];
left join (Data)
max(DPD) as maxDPD
group by CustomerID;
Then use expression similar to this in the chart (the 180 can be swapped to a variable as in your solution) ..