Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I need create a line chart compare goal and actual. The goal goest to year end and actual is just to current month. After I finish this line chart, the actual line continue to year end. Can you help?
Thanks in advance,
Dust
You can check on the current value and stop the line like
=if(sum ( {$<[Ops Month] = {"<=$(vOpsReviewMonth)"}>} [Revenue-Actual]),
rangesum(
sum ( {$<[Ops Month] = {"<=$(vOpsReviewMonth)"}>} [Revenue-Actual]),
above("Actual")
))
[you can also check on the current Month instead of the Value, if the Actual might be zero for some month]
You can also hide the line after a certain point using a appropriate background color.
See attached.
Hope this helps,
Stefan
Hi
I assume that you are using full acumulation on the chart. Rather than doing that, use an expression like
=RangeSum(Above(Sum(Sales), 0, RowNo()))
Depending on your data model, that should do the trick...
Hope that helps
Jonathan
Thank you, Jonathan.
I tried RangeSum and full acumulation both methods. I get the same result. I load two crosstab table both Goal and Actual and joined with Company and Month.
The actual line should stop at current month. The one I have doesn't make any sense.
Thanks,
Dust
I suggest that you post your app - something in the model or expression is making it see zero values rather than missing values for the future months. Bear in mins that a sum() of nulls is 0, nor null.
Regards
Jonathan
Hi Jonathan,
Thank you very much for your help. Please see attached test file. Your help is greatly appreciated.
Thanks,
Dust
Thank you very much, Joanathan.
Please see attached testing file. It may give you more sense. Your help is greatly appreciated.
Dust
You can check on the current value and stop the line like
=if(sum ( {$<[Ops Month] = {"<=$(vOpsReviewMonth)"}>} [Revenue-Actual]),
rangesum(
sum ( {$<[Ops Month] = {"<=$(vOpsReviewMonth)"}>} [Revenue-Actual]),
above("Actual")
))
[you can also check on the current Month instead of the Value, if the Actual might be zero for some month]
You can also hide the line after a certain point using a appropriate background color.
See attached.
Hope this helps,
Stefan
Stefan, Thank you very much. You answer is excetly what I need I pluge on my testing file it works perfectly. I still need add your code to my prod one.
Best regards,
Dust
Stefan,
Can you write another code for month? based on your code, we need let chart show accumulation amoumt to the reviewmonth.
I appreciate your help.
Dust
Sorry, I don't just get what you want, can you tell me the measure using the fields in your sample that you want to accumulate?