Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
Below expression from Sunny :-
RangeSum(Above(RangeSum
(Above(
Sum
(
{
<
year, month, aR={aR}
>
}
[Amount]*1)/Rate, 0, RowNo()))),
-
Sum(
{
<
aR={aR}>
}
TOTAL Aggr(Sum(
{
<
year, month, aR={aR}
>
}
[Amount]*1)/Rate, YearMonth
)))
above expression work fine.
Can some one share with me why it repeat twice on RangeSum(Above :-
RangeSum(Above(RangeSum(Above(
Paul
Hi Paul, in that sample your table on the right doesn't have a row above so it's aonly doing the this part of the expression:
-Sum(TOTAL Aggr(Sum({<year, month>}[Amount]*-1), YearMonth))
Yo can try it leaving only this as expression, it only sums all the amounts... in a wird thing beacuse it shows the negative of a value multiplied by -1 wich can be simplified to:
Sum(TOTAL Aggr(Sum({<year, month>}[Amount]), YearMonth))
And if you want to work based in the (max) year and month selected:
Sum({$<year, month, date={'<=$(=Date(Max(date)))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=Date(Max(date)))'}>}[Amount]), YearMonth))
For the last year you can sum all values until 12 months before the max date:
Sum({$<year, month, date={'<=$(=AddMonths(Max(date), -12))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=AddMonths(Max(date), -12))'}>}[Amount]), YearMonth))
qvf
No idea. Have you tried modifying the expression?
Hi Jona
This expression from sunny , I don't understand how the expression work , so I cannot modify.
Paul
Sent from my iPhone
Hi Paul, the inside RangeSum() sums the values returned by Above, wich returns a value for each of the rows above.
The outside RangeSum sums the values returned to the inside RangeSum and the result returned by the Sum(Aggr()) (wich has a minus sign, so it really substracts)
Hi Rub
Thank you for your explain , not so easy to understand .
Paul
Sent from my iPhone
Yes, it's hard and I can't test, removing the duplicated RangeSum(Above( it will be:
RangeSum(
Above(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, 0, RowNo()),
- Sum({<aR={aR}>} TOTAL Aggr(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, YearMonth))
)
And I'm not sure if this can work, can you try it?
Hi Sir
Great you have try to break the expression into smaller , I will test on Sunday night , as I am going for hiking now .
In fact this is what I need , as I am not able to get the figure I want for 2 month .
Appreciate your help.
Paul
Sent from my iPhone
Hi Ruben
i have tested your expression , it does not give any error.
BUt but now I have issue because I am stuck. Don't know how to break the expression into 2.
Hi Paul, I don't understand what you meant with "break the expression into 2"... Make the expression work for 2 months?
I think I will need a sample to try that or we will spend a lot of time with guessings but here goes one guess:
RangeSum(
Above(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, 0, RowNo()),
- Sum({<year, month, aR={aR}>} TOTAL Aggr(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, YearMonth))
)