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

[vinieme12]

That's because with Suppress Null checked ..you won't see null customer names in straight table so your totals look correct.

Well your data definitely has null values which means your script isn't working as expected you might want to recheck the where clause

where len("Customer Name")>0; and removed the or part

[wossenhh]

Vineeth -  No the Suppress Null hasn't check on the straight table I have been working on.

Do you happen to know what formula to use in order to exclude the Null values other than what I used ? I am loading the data from SQL.

[vinieme12]

You can also try

WHERE NOT ISNULL(Customer Name)

[wossenhh]

Hi Vineeth ,

Thanks and I applied that one you recommend me and it doesn't like it. But, you are correct there was still a null and blank value for customer and other fields like Molecule. I used WHERE NOT([Customer Name] IS NULL or [Customer Name] = ' '  or Molecule IS NULL or Molecule = ' '); and now this give me a valid data's. However the Total YTD calculation still not right.

[sunny_talwar]

I think right now you are doing a or test between Customer Name and Molecule.... So if either of them is not null, the application will still pick the null value for other. So If for a particular row Molecule is not null, but Customer Name is, then it will be picked. May be you want this?

WHERE Len( "Customer Name" ) > 0 AND Len (Molecule) > 0;

[wossenhh]

Hi Sunny - Thanks for your input.

Actually the null and blank are exists in both column. So I used the OR function and excluding them now.

The problem I have is the YTD calculation part. The expression I have is:

SUM({<Fyear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,FMonth=>}(History_Shipments * Price_Price))

Any thought on this?

[sunny_talwar]

Can you check if this is working?

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

[wossenhh]

Sunny - It give me incorrect amount like way over

[sunny_talwar]

Can you share an updated sample to look at this issue?

[wossenhh]

Here it is.

Thanks Sunny