YTD Sales analysis Calculation

[wossenhh]

Hello,

I was trying to show the sales analysis for Fiscal year that shows from Apr to Mar. When I select Customer, Year and Month the sum of sales shows correctly. However If I select just Year, the sales amount shows way over.

The Expression formula that shows in text object looks like below :

=NUM(Sum({<Fyear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

=NUM(sum({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

One thing to mention, this formulas gives me correct answers when I do it on straight table. This happens only on Text object as the requirement is to display on text object.

Can anybody please help?

Thnaks,

Wossen

[sunny_talwar]

Well it would depend on what exactly are you looking to plot on the line chart?

[wossenhh]

If I want just for selected YTD, not MTD

[vinieme12]

This is to avoid null()s in expression are you ok with having them in your data, as they may pose other problems

[wossenhh]

Yes Vineeth -Now I am getting a clean data.

Now I am working on the line chart

[sunny_talwar]

Not sure I understand what you mean?

Wossen Kebede wrote:

If I want just for selected YTD, not MTD

[wossenhh]

Sorry for not being clear Sunny. I used the expression that I have been used for the actual. And it shows me the correct line chart. What I was trying to say was, is that possible just to display only when selected for year so that it just show the line chart from Apr - Mar even if you selected months?

The other thing the expression that you advised me yesterday for Actual/Forecast text object, doesn't work consistently.

=Num(RangeSum(Sum({<fYear={$(vMaxYear)},Date={'<=$(=vMaxDate)'},Month=,Day=,FMonth=,[Customer Name] *={"*"}>}(History_Shipments * Price_Price)), $(vForecast)),'$ #,##0', '.',',')

Any other thoughts?

[sunny_talwar]

1) So for the first thing, are you saying that regardless of whichever month is selected, you would still like to see all the months in the current selected year?

2) Would you be able to share a scenario where the expression is not working? What selections you made, what was the output that you saw and what was the output you expected to see? This will help me understand the issue much better.

[wossenhh]

1- Yes that is exactly what I meant.

2. If you select for the year 2017, it looks right, but if you select for year 2017 for AUG, it doesn't add up correctly.

[sunny_talwar]

1) You just need to ignore selection in FMonth to get this

Actual

Sum({<FMonth>} Aggr(RangeSum(Above(Sum({<FMonth>} History_Shipments * Price_Price), 0, 12)), Month))

Forecast

Sum({<FMonth>} [Forecast_Adjusted Forecast] * Price_Price)

2) What do you want to see when you select Aug? YTD Actual or FMonth's Actual? If you want YTD Actual, your current expression is right because it is summing

    $108,925,644 -> YTD Actual

+ $4,283,383,154 -> Forecast

If you don't want this and instead want to sum

$23,407,615 -> Actual for the month, then you can try this expression:

=Num(RangeSum(Sum({<fMonth,[Customer Name] *={"*"}>} (History_Shipments * Price_Price)), $(vForecast)),'$ #,##0', '.',',')

Which will now give you $4,306,790,768 when you select 2017 and Aug.

Also, on a small note, I think your application is very confusing with the usage of FMonth, fMonth, FYear and fYear and fields such as those. Try to name them in a way that can be easy for you and others to understand.

HTH

Best,

Sunny

[wossenhh]

1. Thanks for your quick reply. I guess I will leave it as it is like whatever selection made to display it accordingly.

2. Yes, I want just sum, and the expression you suggested work, but it is weird that in some cases the last number did not sum up correctly. Check for the year 2017 for Aug. You will see 23,407,615 for Actual, and 4,283,383,184 for Forecast. So the Actual/Forecast shows 4,306,790,768 and also you can check for Oct.

Thanks for your note, and I totally agree with you. I am planning to clean up and make it clear.